3

Database gets created just fine, and the insert works just fine. I'm having problem with the select statement below. It returns no records and no error. A select on any other field works just fine. Can anyone spot what I'm doing wrong?

"create table if not exists data (pkey integer primary key, doc date)"

[db executeUpdate:@"insert into data ( doc) values (?)" , [NSDate date]];

"select * FROM data WHERE doc between '2009-08-23' and '2009-08-23' "
Jordan
  • 21,746
  • 10
  • 51
  • 63
  • I am trying to access using FMDatabase, but I am entering records using a GUI, each time I do not get any records. please check my question and suggest: http://stackoverflow.com/questions/8772601/date-entry-into-database-through-gui-for-date-which-is-readable-by-fmdatabase – Raj Pawan Gumdal Jan 07 '12 at 20:01

3 Answers3

10

The part between '2009-08-23' and '2009-08-23' is always false, because that's a range of zero time, for lack of a better description.

If you want items occurring on exactly 2009-08-23, select between '2009-08-23' and '2009-08-24' which will range from midnight on the 23rd to midnight on the 24th.

Here's an example of this in action:

sqlite> CREATE TABLE t (d DATETIME);
sqlite> SELECT DATETIME('now');
2009-08-24 02:32:20
sqlite> INSERT INTO t VALUES (datetime('now'));
sqlite> SELECT * FROM t;
2009-08-24 02:33:42
sqlite> SELECT * FROM t where d BETWEEN '2009-08-24' and '2009-08-24';
sqlite> SELECT * FROM t where d BETWEEN '2009-08-24' and '2009-08-25';
2009-08-24 02:33:42
Mark Rushakoff
  • 249,864
  • 45
  • 407
  • 398
2

I have had to do this exact type of lookup on sqlite and here is a simple date formatting example to get the start and end of day timestamps for your date.

In my case I am storing the timestamps with this timestamp format in sqlite: "yyyy-MM-dd HH:mm:ss.SSSS". This example uses the current date ([NSDate date]).

NSDateFormatter *dateFormatter = [[[NSDateFormatter alloc] init] autorelease];  
[dateFormatter setDateFormat:@"yyyy-MM-dd"];
NSString *dateString = [dateFormatter stringFromDate:[NSDate date]];
NSDate *startOfDay = [dateFormatter dateFromString:dateString];  
// add a full day and subtract 1 second to get the end of day timestamp
NSDate *endOfDay = [startOfDay addTimeInterval:(60*60*24)-1];
[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss.SSSS"];

You can then use startOfDay and endOfDay for your query values.

paulthenerd
  • 9,487
  • 2
  • 35
  • 29
1

SQLite does string comparison, not date comparison.

So, one way to solve your problem is:

"select * FROM data WHERE doc between '2009-08-23' and '2009-08-23 99' "

Marian
  • 2,617
  • 1
  • 16
  • 13