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

Solution 1:

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

You can do:

orderby appointment_date,
         (casewhen start_time like'% am'then1else2end),
         start_time

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

Solution 2:

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:

SimpleDateFormatdisplayFormat=newSimpleDateFormat("HH:mm");
        SimpleDateFormatparseFormat=newSimpleDateFormat("hh:mma");
        Date date=newDate();
        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);