Date Query Problem

[shahid_146]

I have got date stored in MySQL in this format "Sat, 22 Oct 2005 - 19:30:00" and I want to write a query to select ALL records with DATE coming AFTER the CURRENT DATE or TODAY. In other words I want to select all future dates only. Can anyone help ?

http://www.binarytrends.com

 

[Kate]

Like this ?

select * from your_table  where date_field > CURDATE()

 

[InvertedVertex]

Would this work?

SELECT * from your_table WHERE date_field > date('D, j M Y - h:i:s')

The date is formatted the way you have it.

 

[shahid_146]

date query

thanks but i found that i was storing the date in a varchar field and mysql won't do a date calculation on a varchar in an english formated date. So I have to handle it with code in php.

 

[Kate]

Shahid,
May I ask why you don't store the date field as datetime ?
You can use the my_sql date_format function to format the display of the date in your queries then.

 

[shahid_146]

I made a mistake long ago by making this field a varchar and storing the date and time in a english style date and now I got in trouble. But I have finally got round it

Thanks everyone. I finally got round it by using this :

DATEDIFF(str_to_date(right(myDateField, 22),'%d %b %Y'),CURDATE())>0


So if anyone ever makes a mistakes by making a date field VARCHAR, this is the solution to compare it with current date

Thanks

http://www.binarytrends.com

 

[Kate]

Fine. If I were you I would run a query to convert the values to a more convenient (date) format :imho: :hehe: