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).

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 — xyzzycoder, Jan 16 '10 at 08:01

score 4 · Accepted Answer · answered Aug 19 '09 at 21:03

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.

score 1 · Answer 2 · edited Jul 16 '13 at 16:34

1

You can always do select Top X.

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

edited Jul 16 '13 at 16:34

Tim Cooper

answered Aug 19 '09 at 20:33

rkb

2 Answers2