CODEWITHSUNDEEP
iphoneobjective-csqlite
user306766
user306766

Reputation:

SQLite3 - select date range not working

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

Upvotes: 1

Views: 1247

Answers (2)

raptor10
raptor10

Reputation: 11

you might also try a different more natural way (natural to sqlite):

select date(..date.fld...,'start of year') = '2011-01-01' ; 
  //any 2011 date will get selected
select date(..date.fld...,'start of year') >= '2011-01-01'; 
  // 2011, 2012 etc

to see other date functions check this link: www.sqlite.org/lang_datefunc.htm

Actually the date.fld seems to be any field that has date data in it whether string, utf, integer, whatever but implementations may vary.

I use sqlite3 with python 2.6 and get consistent results...

Upvotes: 1

Doug Currie
Doug Currie

Reputation: 41180

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.

Upvotes: 1

Related Questions