SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Date query

  1. #1
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Howdy all,
    I need to be able to store a date in a MySQL table but print it out as "27th Apr 2001". No problem, but I also need to be able to test the date to see if it is more than three days ago.

    Any ideas of the best way to do it?

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is pretty simple:

    select * from tablename where datefield < DATE_SUB(CURDATE(), INTERVAL 3 day);
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again freddy!
    I know about the comparison statement that you gave me earlier, but what format would the date have to be in the MySQL table for that to work?

    Would "27th Apr 2001" still work?

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, you should never store your dates in any format excpet a date type field in MySQL, you can always format the date to be pretty on the way out of the query, butusing a date field will give you tons of useful functions in MySQL. Example of how you should store a date and how to retrieve and format it

    Stored:
    04-27-2001

    To format it in the query


    select DATE_FORMAT(datefield, '%D %b %Y') as newdate from tablename;

    Then it will come out like

    27th Apr 2001
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to make sure I'm doing the whole date thing right, could somebody confirm for me

    I have a date field, and I insert into that field like so
    INSERT INTO table (date) VALUES(CURDATE())

    is that way okay, just want some confirmation.

    ta.

  6. #6
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! Thanks freddy, I didn't know it was that easy!

    Much appreciated, as always.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •