SQLite Forum

DATE type

DATE type

(1) By birdman518 on 2021-12-28 14:29:33 [link] [source]

I have the following table:

CREATE TABLE votehistory ( voterId integer NOT null, county TEXT NOT NULL, electionDate DATE NOT NULL, electionType TEXT NOT NULL, historyCode TEXT NOT NULL );

The data looks like this:

206869614 ROC 01/14/2014 PRI Y
206864773 ROC 01/14/2014 PRI B
206852342 ROC 01/14/2014 PRI A

The question I have is that the DATE field looks correct, but behaves as if it is a text field for ORDER BY. I am pretty sure that the data loaded was in exactly that format (MM/DD/YYYY). To get it to sort I had to massage it this way: substr(B.electionDate,7,4)||"-"||substr(B.electionDate,1,2)||"-"||substr(B.electionDate,4,2) as electionDate,

What am I missing? If sqlite understands 'electionDate' is a DATE, can it really not order it?

(2) By Stephan Beal (stephan) on 2021-12-28 14:33:50 in reply to 1 [source]

What am I missing?

That sqlite has no DATE type:


The conventional way to store dates as strings in a sensibly sortable manner is to store them in ISO-8601 format.

(3) By birdman518 on 2021-12-28 16:20:38 in reply to 2 [link] [source]

OOPS! My bad... I will look at doing an update to the ISO format..