mySQL and dates.

[Anon]

Hey all,

I am writing a script that lets me put in news articles. I have a field called "viewdate" which would have the date like this: November 30, 2001

My question is this. I am trying to get the script to display the last say 5 news stories as long as their view date is today or past.

So I might have one I just enter as viewdate February 22, 2002 and I do not want it to appear till then.

Any clues? Do I need dates in different formats?

Thanks!

[murp32]

Hiya...

Ideally, you would want to have dates in the MySQL native format, which is YYYY-MM-DD HH:MM:SS.

This makes much more sense to MySQL, and it's far easier to work with dates with this format.

You normally would do the processing of the date, to get it to MySQL's date format, when you insert your record. Then you format the date the way you want it using MySQL's DATE_FORMAT() function - which is really good.

There should be no reason why you HAVE to store dates in textual format.

Hope that helps.

Matt

[iifs044]

SELECT *
FROM NewsItems
WHERE ViewDate <= CutoffDate
ORDER BY ViewDate
LIMIT 5

This would give the five most recently viewable news stories who's ViewDate is less than the "CutoffDate." This requires, however, that your dates be stored in some sort of binary format, or a string format that collates chronologically. Using a textual date format in a VARCHAR() field will not work. As Matt mentioned, you can use mySql's various date functions to format the date to be displayed any way you want ... it's how it's stored which matters to the query.

Does that help? More Questions?

-- Mitch

[Anon]

ahh ok. Thanks guys.

Is there a faq somewhere with generating and decoding the mysql date?

I read that it is different than just the timestamp.

Thanks!