Date_Format issues.

[natpres]

I am having issues with a MySql query

SELECT *,DATE_FORMAT(startdate, '%d-%m-%Y')AS startdate, startdate as sortdate 
from {$table} 
WHERE DATE_FORMAT(startdate, '%d-%m-%Y') >= DATE_FORMAT(now(), '%d-%m-%Y') 
ORDER BY sortdate ASC

It will not show any dates in the future is the day is less then the current day.

For example it will show the entry with a date of 2010-05-20
But not 2010-05-10 Even though the date is in the future.

The date format within MySql is datestamp. 0000-00-00 00:00:00 and called startdate.

Any ideas?

[Atli]

Hey.

Drop the DATE_FORMAT functions from the WHERE clause. That function converts the dates into strings, which MySQL then compares as strings, not dates. That's why you are getting inconsistent results.
[code=mysql]WHERE startdate >= now()[/code]

You only have to use functions like DATE_FORMAT for stuff that is meant to bee seen.

[natpres]

Works 100%

Thank you for your reply. Amazing how simple something is when someone else points something out!

Cheers