SQLite date compare

Question

How do I compare dates in SQLite database which are stored in DD-MMM-YYYY format e.g. 10-OCT-2017?

I want to compare dates and select rows of particular date and adjacent dates.

Answer 1

Short answer:

Painfully. You must convert the datestring, particularly the character month, into a numeric value, then you can compare the date values.

... Or more easily, by storing your dates using a format like “2018-01-01” which can be natively compared within the SQL via SQLite date functions.

Longer Answer

This page shows all the SQLite SQL date-time functions available. None of them produce or manipulate character month values. So your options are:

1) Select a group of records via SQL into a dataset; and then use your programming language to convert the date values in the dataset to a format whose values are comparable; then compare them.

This would have poor performance for anything except very small data queries, but would probably be reasonably simple to implement, and have no data conversion necessary. (To specifically answer the question you asked, this is the best solution for an app selecting few and small datasets.)

2) Create a SQLite function to do the date format conversion. To do that, you use SQLite's C API. See more how-to discussion here.

I haven't done this myself, and I wouldn’t recommend this route just due to the learning curve, but also due to the type & purpose of SQLite and its capabilities. (To specifically answer the question you asked, this is the best solution for not few and not small datasets.)

3) (This option does not answer your specific question) Convert your stored date values to a natively SQL comparable format. Like: “2018-01-01”. Then you can use the SQLite date-time functions for adjacent date comparisons.

Sample:

 select mySQLTableDate, myOtherSQLTableDate
      date(mySQLTableDate,'+1 day'),  -- YYYY-DD-MM stored format
      date(strftime('%Y-%m-%d', mySQLTableDate),'+1 day') -- many stored formats
  from mySQLTable
  where select mySQLTableDate = date(myOtherSQLTableDate,'+1 day') 

Answering your question in terms of the goal rather than the specific question :) , my recommendation is to Use This Solution, especially if you are scanning a lot of data. See more about SQLite Date types here, but for dates with no time, I just store them as the string “2018-01-01”. I’m working with .js, and this is very simple to convert to/from a .js Date object.