How to sort the time in table using my sql query

Question

how to sort the table for time . that is from 6.00 AM t0 10.00 PM

 name    source destination time 
 gokul   xxx    yyyy        10.00 PM
 abc     xxx    yyyy        6.00 AM

I tried this query in side subquery still not working

Select * from (
  Select * from trips where date = '27-09-2013' 
  and time like '%PM' or '%pm' ORDER BY time
) AND (
  Select * from trips where date = '27-09-2013' 
  and time like '%AM' or '%am' ORDER BY time
) AS TIME 
ORDER BY TIME(time) DESC'

is time in string form, with AM and PM?, or in actual timestamp form? — AdrianBR
– AdrianBR, Commented Sep 27, 2013 at 10:54
its a varchar @AdrianBR and i tried ORDER BY and LIKE but its not wokring — gokul
– gokul, Commented Sep 27, 2013 at 11:09

Code Lღver · Accepted Answer · 2013-09-27 11:47:25Z

3

use this query:

SELECT * FROM tabel_name 
    ORDER BY STR_TO_DATE(`time`,'%h.%i%p');

SQL FIDDLE working example.

EDITED:

After seeing your query i am posting the modified query for you.

SELECT * FROM trips 
    WHERE `date` = '27-09-2013' 
       AND ((`time` like '%PM' OR '%pm') 
          OR (`time` like '%AM' OR '%am')) ORDER BY STR_TO_DATE(`time`,'%h.%i%p') DESC

edited Sep 27, 2013 at 11:47

answered Sep 27, 2013 at 11:11

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Code Lღver Over a year ago

@gokul can you post your table structure also.

Collectives™ on Stack Overflow

How to sort the time in table using my sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related