How to select rows by date in sqlite

Question

I have to select all rows from database by just passing date. For example to get all rows that have date 10/23/2012

In sqlite db I store this in DATE column:

01/01/1900 11:00:00 AM

I have tried to get by using date() but I get nothing for date:

select itemId, date(dateColumn) from items

So all I need is to compare only dates but can't find how to do this in sqlite.

Answer 1

Firstly, format your dates to the ISO-8601 standard. Wrap it in Date() to ensure it gets processed as a DATE. Finally, construct your range so that it will include everything from 12:00am onwards until just before 12:00am the next day.

select itemId, dateColumn
from items
where dateColumn >= date('2012-10-23')
  AND dateColumn <  date('2012-10-23', '+1 day')

SQLite columns are not typed. However, if you compare the column to a DATE as shown, it is sufficient to coerced the column data into dates (null if not coercible) and the comparison will work properly.

Example on SQLFiddle:

create table items (
  itemid, datecolumn);
insert into items select
  1,'abc' union all select
  2,null union all select
  3,'10/23/2012 12:23' union all select
  4,'10/23/2012' union all select
  5,'2012-10-23 12:23' union all select
  6,'2012-10-23' union all select
  7,'2012-10-24 12:23' union all select
  8,'2012-10-24' union all select
  9,date('2012-10-24 12:23') union all select
  10,date('2012-10-24');

Results:

itemid  datecolumn
5       2012-10-23 12:23
6       2012-10-23

Note that although rows 3 and 4 appear to be dates, they are not, because they do not conform to ISO-8601 formatting which is the only format recognized by SQLite.