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

  • 3 Contributors
  • 7 Replies
  • 172 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

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

Edited by pritaeas because: n/a
Member Avatar for sam230

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

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

Edited by pritaeas because: n/a
Member Avatar for sam230

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

didnt get any such function

Member Avatar for pritaeas

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.

Edited by diafol because: n/a
Member Avatar for 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
Edited by pritaeas because: n/a
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.