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

Question

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.

Answer 1

Better solution would be to store your time in 24 hour format in one more column and that column should be of type DATETIME not varchar.

You can convert your time using:

        SimpleDateFormat displayFormat = new SimpleDateFormat("HH:mm");
        SimpleDateFormat parseFormat = new SimpleDateFormat("hh:mma");
        Date date=new Date();
        try {
            date = parseFormat.parse(AmPmTimeFormatColumn);
        }catch (Exception e){}
        values.put("24HourFormatColumn", displayFormat.format(date));

Then order like this:

select * from table_name order by DATETIME(24HourFormatColumn);

Answer 2

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.