How can I query a date from a datetime object in SQLite?

Question

Here is my attempt:

rs = statement.executeQuery("select * from geninfo where DeviceTag='" + deviceTag + "' and timestamp='date('"+fromYear + "-" +fromMonth + "-" + fromDay + "')';");

It will look similar to this:

select * from geninfo where timestamp='date('2013-07-08')';

The error is (near "2013": syntax error)

The value stored is "2013-07-08 09:08:51"

I want to select all the data of a certain day. Also, would it be easier to just store another column as a date object?

Does [this](http://stackoverflow.com/questions/1975737/sqlite-datetime-comparison) answer help? — n0741337, Jul 08 '13 at 16:23

score 0 · Accepted Answer · answered Jul 08 '13 at 16:34

You must not surround the date function with single quotes, which are string delimiters. What you have in the SQL statement is the string date(, the numbers 2013, 07, and 08, which are subtracted from each other, and the string ).
The function date converts the string 2013-07-08 into the string 2013-07-08. Comparing that to the string 2013-07-08 09:08:51 will fail. You want to strip the time from the string that has the time fields:
```
SELECT *
FROM geninfo
WHERE DeviceTag = ?
  AND date(timestamp) = '2013-07-08'
```

How can I query a date from a datetime object in SQLite?

1 Answers1