SQLite DateTime comparison

To solve this problem, I store dates as YYYYMMDD. Thus, where mydate >= '20090101' and mydate <= '20050505'

It just plain WORKS all the time. You may only need to write a parser to handle how users might enter their dates so you can convert them to YYYYMMDD.

SQLite doesn't have dedicated datetime types, but does have a few datetime functions. Follow the string representation formats (actually only formats 1-10) understood by those functions (storing the value as a string) and then you can use them, plus lexicographical comparison on the strings will match datetime comparison (as long as you don't try to compare dates to times or datetimes to times, which doesn't make a whole lot of sense anyway).

Depending on which language you use, you can even get automatic conversion. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)

I have a situation where I want data from up to two days ago and up until the end of today. I arrived at the following.

WHERE dateTimeRecorded between date('now', 'start of day','-2 days') 
                           and date('now', 'start of day', '+1 day') 

Ok, technically I also pull in midnight on tomorrow like the original poster, if there was any data, but my data is all historical.

The key thing to remember, the initial poster excluded all data after 2009-11-15 00:00:00. So, any data that was recorded at midnight on the 15th was included but any data after midnight on the 15th was not. If their query was,

select * 
  from table_1 
  where mydate between Datetime('2009-11-13 00:00:00') 
                   and Datetime('2009-11-15 23:59:59')

Use of the between clause for clarity.

It would have been slightly better. It still does not take into account leap seconds in which an hour can actually have more than 60 seconds, but good enough for discussions here :)

Right now i am developing using System.Data.SQlite NuGet package (version 1.0.109.2). Which using SQLite version 3.24.0.

And this works for me.

SELECT * FROM tables WHERE datetime 
BETWEEN '2018-10-01 00:00:00' AND '2018-10-10 23:59:59';

I don't need to use the datetime() function. Perhaps they already updated the SQL query on that SQLite version.

You could also write up your own user functions to handle dates in the format you choose. SQLite has a fairly simple method for writing your own user functions. For example, I wrote a few to add time durations together.

Here is a working example in C# in three ways:

  string tableName = "TestTable";

  var startDate = DateTime.Today.ToString("yyyy-MM-dd 00:00:00"); \\From today midnight
  var endDate = date.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss"); \\ Whole day

  string way1 /*long way*/ = $"SELECT * FROM {tableName} WHERE strftime(\'%s\', DateTime) 
  BETWEEN strftime('%s', \'{startDate}\') AND strftime('%s', \'{endDate}\')";
  
  string way2= $"SELECT * FROM {tableName} WHERE DateTime BETWEEN \'{startDate}\' AND \'{endDate}\'";

  string way3= $"SELECT * FROM {tableName} WHERE DateTime >= \'{startDate}\' AND DateTime <=\'{endDate}\'";