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.
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.
WHERE dateColumn BETWEEN date('2012-10-23') AND date('2012-10-23', '+1 day') ?
>= and < pattern is specifically designed to end just before the stroke of midnight of the next day.In SQLite, there is no datatype DATE, it's stored as strings. Therefor, the Strings have to match exactly to be equal.
Since we don't want that, you'll want to "cast" the values from the date-column to pseudo-date and also cast your argument to a pseudo-date, so they can be compared:
SELECT itemId FROM items WHERE date(dateColumn) = date("2012-10-22");
Note that the date-command takes dates formated as YYYY-MM-DD, as further explained in an answer to this older question. The question also shows that you can use the BETWEEN x AND y-command to get dates, matching a range.
SELECT itemId,dateColumn FROM items WHERE dateColumn=date('YYYY-MM-DD HH:MM:SS');
select itemId,dateColumn from items where dateColumn = @date
WHERE-clause to select only dates matching X