CODEWITHSUNDEEP
phpmysqldatetime
Hearner
Hearner

Reputation: 2729

"order by date" with string type

My question would look dumb but i haven't find any solution that works. I have in my DB DateTime in the format d/m/Y H:i:s (day/month/year hour:min:sec) which is a String.

I need to order by DateTime a table but obviously it orders it by the day 08/06/2015 11:25:37 is before 14/05/2015 10:18:20. I tried to convert DateTime into a date but it does not work.

Is there a way to convert it properly or to order it by years then month then day then hours etc. ?

Upvotes: 2

Views: 162

Answers (2)

Jens
Jens

Reputation: 69440

You have to use str_to_date to convert the string to date and than you can use it in order by:

SELECT * from `table` order by STR_TO_DATE(column_name,'%d/%m/%Y %H:%i:%s');

For more information, see the mysql documentation

You should think about changing the type of your column to a datetime type.

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can order by converted value:

select * from TableName
order by STR_TO_DATE(ColumnName, 'd%/m%/Y% H%:i%:s%')

Fiddle http://sqlfiddle.com/#!9/f5003/1

Upvotes: 4

Related Questions