SQLite3 - select date range not working

Question

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

Answer 1

SQLite has no date type (though it has good date functions). Your comparisons are done on strings, To make them work correctly you should change your format to yyyyMMdd or yyyy-MM-dd so they sort in date order.