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

Recommended Answers

All 7 Replies

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

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

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().

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

didnt get any such function

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

Member Avatar for 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.

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.