CODEWITHSUNDEEP
sqlite
StephaneM
StephaneM

Reputation: 4899

Ordering dates descending in 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

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

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?

Upvotes: 1

Views: 1527

Answers (1)

CL.
CL.

Reputation: 180172

These strings are not in one of the supported date formats.

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.

Upvotes: 1

Related Questions