SQLite3 - select date range not working

Posted by iFloh on Stack Overflow See other posts from Stack Overflow or by iFloh
Published on 2010-04-22T14:54:46Z Indexed on 2010/04/22 16:33 UTC
Read the original article Hit count: 167

Filed under:

sqlite

|

objective-c

|

iphone

yet anotherone that gives me grief.

In a SQLite3 DB Select I query for a date range specified in (NSDate *)fromDate to (NSDate *)toDate

const char *sql = "SELECT * FROM A, B WHERE A.key = B.key AND A.date between ? and ?";

After opening the DB I run the query in Objective-C as follows:

NSDateFormatter *tmpDatFmt = [[[NSDateFormatter alloc] init] autorelease];
[tmpDatFmt setDateFormat:@"dd-MM-yyyy"];

sqlite3_stmt *stmt;

if(sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
    NSLog(@"From %s to %s;", [[tmpDatFmt stringFromDate:fromDate] UTF8String], [[tmpDatFmt stringFromDate:toDate] UTF8String]);

    sqlite3_bind_text(stmt, 1, [[tmpDatFmt stringFromDate:fromDate] UTF8String], -1, SQLITE_STATIC);  // first '?'
    sqlite3_bind_text(stmt, 2, [[tmpDatFmt stringFromDate:toDate]   UTF8String], -1, SQLITE_STATIC);  // second '?'

    while(sqlite3_step(stmt) == SQLITE_ROW) {
        NSLog(@"Success");}

In the database I have several records that match the date range:

12-04-2010 = in seconds 1271059200
13-04-2010 = in seconds 1271145600
13-04-2010 = in seconds 1271152800
14-04-2010 = in seconds 1271267100

When I run it the first NSLog shows

From 2010-04-01 to 2010-04-30

my problem is the records are not selected (no "Success" shows in the log) and I don't understand why.

earlier I had miscalculated the dates 2 days later as

14-04-2010 = in seconds 1271232000
15-04-2010 = in seconds 1271318400
15-04-2010 = in seconds 1271325600
16-04-2010 = in seconds 1271439936

These dates worked fine (4 x "Success in the log). I am puzzled ...

Developer IT