SQLite: Need Date >= (x) number of days from today

Question

Trying to select a date (x) days from today's date, where the date would be start of day (e.g. 12:00am that day).

For example, a query with date 5 days earlier..

@"select pkey, dateofmod from data WHERE dateofmod >= date('now', '? days')" ,  [NSNumber numberWithInt:-5];

doesn't seem to work. (using FMDB).

Answer 1

You can always do slect Top X.

@"select TOP X from data WHERE dateofmod >= date('now', '? days')" , [NSNumber numberWithInt:-5];

Answer 2

Is this what you need?

sqlite> SELECT date( julianday(date('now')));
2009-08-19
sqlite> SELECT date( julianday(date('now'))+2);
2009-08-21

julianday does "round" to midnight:

sqlite> SELECT datetime( julianday(date('now')));
2009-08-19 00:00:00
sqlite> SELECT datetime( julianday(date('now'))+2);
2009-08-21 00:00:00

Usually with SQLite you want to use julianday if you are adding a number of days to a date.

SQLite Date and Time Functions

Answer 3

You might find the details on this question/answer useful.

http://stackoverflow.com/questions/1711504/how-get-datetime-column-in-sqlite-objecite-c/1711591#1711591