FMDB queries with NSDate execute but do not take effect

Any query I execute through FMDB that include NSDates execute but do not return result sets or make edits to the database. I am working in the ios 4.3 simulator.Other Queries that do not stipulate a date execute correctly. Heres an exerpt of the code that i use:

NSDateFormatter *dateFormatter = [[NSDateFormatter alloc]init];
[dateFormatter setDateFormat:@"yyyy-MM-dd hh-mm-ss"];

NSDate *start = [dateFormatter dateFromString:@"2011-07-18 06:40:21" ];
NSDate *end = [dateFormatter dateFromString:@"2011-07-18 06:41:19"];

[dateFormatter release];

FMDatabase* db = [FMDatabase databaseWithPath:appDelegate.databasePath];
if (![db open]) {
NSLog(@"Could not open db.");
}

[db beginTransaction];
[db executeUpdate:@"delete from time_stamp_table where time_stamp >= ? 
and time_stamp <= ?",start,end];

NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
[db commit];
[db close];

The schema states time_stamp in the time_stamped_table thusly:

create table time_stamp_table{
id INTEGER PRIMARY KEY,
time_stamp DATETIME NOT NULL
}

When I make these queries in sqlite3 on the command line, using the database stored on the simulated Application context, they work. Is there 开发者_如何学编程some special preperation for querying by NSDate with FMDB that I am missing?

Query like

 SELECT *  from Time_Stamp where updatedTime >= DATE('2011-05-17 06:40:21') AND updatedTime <= DATE('2011-07-19 06:40:21')

fetches the correct records while comparing the dates. You might take some hint from this and change your query something like

 [db executeUpdate:@"delete from time_stamp_table where time_stamp >= DATE(?) 
and time_stamp <= DATE(?)",start,end];

or

 [db executeQuery:[NSString stringwithFormat:@"delete from time_stamp_table where time_stamp >= DATE('%@') 
and time_stamp <= DATE('%@')",start,end];

I have not tested the queries so You will have to test it yourself. Hope it helps

Try to avoid parsing a NSDate to a SQL-Query. Rather try parsing a NSString in a format sqlite may understand: '2011-07-18 06:41:19'. If you would like sqlite to compare the values you have to use the a specific date function. Sqlite3 provides you the DATE, TIME or DATETIME functions to convert your strings.

Secondly, to correct Rahul Sharma, using the DATE function to compare a TIMESTAMP would fail because TIMESTAMP is saving both date and time. Therefore the DATETIME function should be used.

[db executeUpdate:@"delete from time_stamp_table where time_stamp >= DATETIME(?) 
                                                   and time_stamp <= DATETIME(?);",
                    @"2011-07-18 06:40:21",
                    @"2011-07-18 06:41:19"]; 

May be you have used executeQuery method. Delete operation belongs to updating operation. At first, I've used executeQuery, but couldn't find the problem even checking the property type once and once again. Finally executeUpdate fixes it.