Search code examples
iossqlsqlitedatetimeswift3

How to order by date/time with AM/PM in SQLITE

My date format is: "yyyy-MM-dd" (2017-03-23)

My time format is: "hh:mm a" (10:15 pm)

If in MYSQL you can perform this to convert time with am/pm:

SELECT * FROM table_name ORDER BY STR_TO_DATE(timeField,'%h.%i%p');

How can you perform this in SQLITE?

I tried this but didn't work:

SELECT appointment_date, start_time FROM appointment order by appointment_date, DATE(start_time, '%h:%i %p')

Result: Image Link

Supposedly AM should be first than PM because the default is ASC, I tried using DESC as well but it didn't properly arranged the result.

Solution

  • You seem to be storing the start_time() value in a string.

    You can do:

    order by appointment_date,
         (case when start_time like '% am' then 1 else 2 end),
         start_time

    SQLite doesn't really support am/pm in date/time formats. But, this is easy enough to accomplish with like.