[RESOLVED] Mysql DATE_FORMAT problems

DeathfireD

I've made a tag cloud for my dates and a second page where, after you click on a tag it displays the data found in the database for that day. Now this method has worked up till this year. For some reason once it turned 2008 the second page that displays the data no longer works correctly but when clicking on 2007 items in the tag cloud they load fine. So heres the gist of what I'm doing. In mysql I have a field called date which saves the date and time (ex. 2008-01-09 18:19:00 ). When I click on a Tag I just want to display the data for that day (ie. 2008-01-09) and order it by the date and time. So I've been doing it with this line

$sql = "SELECT * FROM `headlines` WHERE DATE_FORMAT(date, '%Y-%c-%d') = '$date' ORDER BY date DESC LIMIT 0, 30";

But for some reason it does not work with dates other then 2007 now...can anyone explain how I can fix this. Btw variable $date equals the date a user clicks on, so for example 2008-01-25.

I hope I was clear enough.

NogDog

What is the value of $date?

DeathfireD

Btw variable $date equals the date a user clicks on, so for example 2008-01-25.

So it's any date in this format year-month-day

NogDog

DATE_FORMAT(date, '%Y-%c-%d') would result in the string '2008-1-25', which would not match the string '2008-01-25'. You would need to use '%Y-%m-%d' if you want a yyyy-mm-dd format. That being said, it would be simpler to just compare the date field directly to the date string, assuming that string will be in an appropriate format (e.g.: the '2008-01-25' format you suggest will be used for $date):

. . . WHERE date = '$date' . . .

DeathfireD

wow...how could I miss that. %m did the trick, thanks allot.

PS. Can I really compare the date field with the $date string if the mysql date has hours, mins, seconds in it's format? I don't think that works out.

Ie. mysql date equals 2008-01-09 18:19:00
and I want all info that came in on 2008-01-09.

BlueKai

I always use date_format for that too - if there's a better way, I'd be interested...

NogDog

If using MySQL 4.1.1 or later, you could use the DATE() function:

. . . DATE(date) = '$date' . . .

DeathfireD

hmm thanks, I should probably upgrade mysql 🙂