在sqlite中按降序排列日期 [英] Ordering dates descending in sqlite

问题描述

我在sqlite中有下表:

I have the following table in sqlite:

CREATE TABLE LICENSE (OBJECT_ID           INTEGER PRIMARY KEY  AUTOINCREMENT,
                      LICENSE             BLOB NOT NULL,
                      NAME                VARCHAR(255), 
                      TYPE                VARCHAR(255) NOT NULL,
                      EXPIRATION_DATE     DATE,
                      CREATION_DATE       DATE NOT NULL)

我想显示所有许可证，按从最近到最旧的顺序排列.我使用这个简单的查询:

I want to display all licenses, ordering them from the most recent to the oldest one. I use this simple query:

SELECT * FROM license order by date(CREATION_DATE) desc

令人惊讶的结果是这样的(我只向您展示了 creation_date 列):

Surprisingly the result is this (I only show you the creation_date column):

11 Sep. 2014 13-59-07
17 Sep. 2014 15-39-26
17 Sep. 2014 17-48-05
18 Sep. 2014 09-59-49
06 Oct. 2014 15-18-44
06 Oct. 2014 15-40-22

所以我试图在我的查询中用 asc 替换 desc，但我得到了完全相同的结果.

So I 've tried to replace desc by asc in my query but I get the very same result.

我怎样才能得到正确的订单?

How can I get the right order?

推荐答案

这些字符串不在 之一中支持的日期格式.

也许可以使用 substr() 来提取日期字段，并且CASE，用可排序的数字替换月份字符串，但最好是首先将值以适当的格式存储在数据库中.

It might be possible to use substr() to extract the date fields, and CASE, to replace the month strings with sortable numbers, but it would be a better idea to store the values in a proper format in the database in the first place.

这篇关于在sqlite中按降序排列日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！