how to find max date through query

[sam230]

i have a date column where dates are saved in varchar format like this
17-03-2011 :: 11:19:48

i want to find last date through query..

please help

thanks

[pritaeas]

Convert your varchar to a date and use ORDER BY DESC, or GROUP BY and MAX()

[sam230]

sorry i cant change the database. is there anyway to explode date format in mysql.

[pritaeas]

You can do it in a query, I did not mean for you to change the database. See the manual entry on string functions. You can use SUBSTR() together with CAST().

[sam230]

Cast() function..???? :O

didnt get any such function

[pritaeas]

It's a mysql function, you can use it in your query.

[diafol]

SELECT `field1`, `field2`, STR_TO_DATE(`datefield`,'%d-%m-%Y :: %h:%i:%s') AS mydate FROM `table1` ORDER BY mydate;

Don't know if that will work. Can you order on the calculated field?? Sorry brain frazzled.

[pritaeas]

I think you need to do:

SELECT STR_TO_DATE(`datefield`, '%d-%m-%Y :: %h:%i:%s') AS mydate
FROM `table1` 
ORDER BY STR_TO_DATE(`datefield`, '%d-%m-%Y :: %h:%i:%s') DESC LIMIT 1