How to select rows by date in sqlite

Question

I need to select all rows from the database, just skipping the date. For example, to get all rows with the date 10/23/2012

In sqlite db, I store this in the DATE column:

 01/01/1900 11:00:00 AM 

I tried to get using date() , but I got nothing for the date:

 select itemId, date(dateColumn) from items 

So, I only need to compare the dates, but I can not find how to do this in sqlite.

Answer 1

First, format dates to the ISO-8601 standard. Wrap it in Date () to make sure it is treated as DATE. Finally, build your assortment so that it includes everything from 12:00 until the next morning until the next morning.

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

SQLite columns are not printed . However, if you are comparing a column with DATE, as shown, it is sufficient to force the column data in dates (null if they are not valid), and the comparison will work correctly.

SQLFiddle example:

 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 lines 3 and 4 display as dates, they are not, as they do not conform to the ISO-8601 formatting, which is the only format recognized by SQLite.

Answer 2

SQLite does not have a DATE data type; it is stored as strings. To do this, the Lines must match exactly.

Since we don’t want this, you need to "distinguish" the values ​​from the date column to the pseudo-data, and also pass your pseudo-range argument, so you can compare them:

 SELECT itemId FROM items WHERE date(dateColumn) = date("2012-10-22"); 

Note that the date command accepts dates created as YYYY-MM-DD , as explained in response to this old question . The question also shows that you can use the BETWEEN x AND y command to get dates that match a range.

Answer 3

 SELECT itemId,dateColumn FROM items WHERE dateColumn=date('YYYY-MM-DD HH:MM:SS'); 

SQLite Reference

Answer 4

select itemId, dateColumn from the elements where dateColumn = @date