CODEWITHSUNDEEP
mysqlsqltimesql-order-by
QWERTY
QWERTY

Reputation: 2315

SQL time column does not sort in ascending order

I was having some trouble when trying to write an SQLstatement which sorted by time column which is in varchar format in ascending order. Here is my SQL statement:

SELECT mrtpopTime, mrtpopAmt
FROM tm_mrtpop
WHERE mrtpopName = ''
ORDER BY mrtpopTime

And I am getting these results:

enter image description here

As you can see from the picture, it was sorted by character by character. Is there anyway to sort it like:

0:00, 1:00, 2:00, 3:00 all the way to 23:00

Any ideas? Thanks in advance.

Upvotes: 0

Views: 608

Answers (2)

Naved Munshi
Naved Munshi

Reputation: 507

As the mrtpopTime is in varchar it won't sort. Hence cast it into float and order by

SELECT  mrtpopTime, mrtpopAmt
FROM tm_mrtpop
WHERE mrtpopName = ''
ORDER BY 'cast(mrtpopTime as float) time'

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300589

Convert varchar time column to a time (or dummy date plus time) and order by that:

SELECT mrtpopTime, mrtpopAmt
FROM tm_mrtpop
WHERE mrtpopName = ''
ORDER BY STR_TO_DATE(mrtpopTime, '%H:%i')

Upvotes: 3

Related Questions