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 do this to convert time with am / pm:

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

How can you do this in SQLITE?

I tried this but did not work:

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

Result: Image link

Presumably, AM should be the first than PM, because by default ASC, I also tried to use DESC, but it did not order the result correctly.

Answer 1

It seems you are storing the value start_time()in a string.

You can do:

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

SQLite am/pm /. like.

Answer 2

24- , DATETIME, varchar.

, :

        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));

:

* table_name DATETIME (24HourFormatColumn);