如何在SQLITE中使用AM / PM按日期/时间排序 [英] How to order by date/time with AM/PM in SQLITE

问题描述

我的日期格式为： yyyy-MM-dd（2017-03-23）

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

我的时间格式为： hh：mm a（10 ：15 pm）

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

如果在MYSQL中，您可以执行此操作以将时间转换为am / 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');

如何在SQLITE中执行此操作？

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

结果：
图片链接

应该说AM应该先于PM因为默认值是ASC，所以我也尝试使用DESC，但是它没有正确安排结果。

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.

推荐答案

将 start_time（）值存储在字符串中。

您可以执行以下操作：

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

SQLite确实不支持日期/时间格式的am / pm。但这很容易用 like 完成。

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

这篇关于如何在SQLITE中使用AM / PM按日期/时间排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！