How to insert date using NSDate object?

[RajChanchal]

I am using following code to insert a record into a table scream_detections. The table contains a column Date_Time of Date type.

BOOL result;
NSString *querry = @"INSERT INTO scream_detections (Date_Time) VALUES (?,?,?)";
result = [database executeUpdate:querry,[NSDate date]];

The Date_Time column shows the value Invalid Date.

Note: databaseis a valid FMDatabaseobject.

[ccgus]

FMDB converts dates to floats, so that's potentially the problem (I don't believe there was a date type when FMDB was originally created).

[robertmryan]

@ccgus - AFAIK, there's still no native date type in SQLite. See Datatypes in SQLite Version 3. Maybe I'm not understanding your comment.

@RajChanchal -

1. I assume that's not the actual SQL (you have too many ? placeholders).
2. When you say Date_Time column shows the value "Invalid Date", where precisely is it showing you that? How are you checking that value?

This works fine:

NSURL *fileURL = [[[NSFileManager defaultManager] URLForDirectory:NSDocumentDirectory inDomain:NSUserDomainMask appropriateForURL:nil create:false error:nil] URLByAppendingPathComponent:@"test.sqlite"];

[[NSFileManager defaultManager] removeItemAtURL:fileURL error:nil];         // remove it in case we're running this multiple times

FMDatabase *database = [FMDatabase databaseWithPath:fileURL.path];
BOOL success = [database open];
NSAssert(success, @"Unable to open %@", fileURL);

success = [database executeUpdate:@"CREATE TABLE scream_detections (Date_Time DATE);"];   // note, it really isn't a true DATE type, but it doesn't matter
NSAssert(success, @"Unable to create table: %@", [database lastErrorMessage]);

success = [database executeUpdate:@"INSERT INTO scream_detections (Date_Time) VALUES (?);", [NSDate date]];
NSAssert(success, @"Unable to insert value: %@", [database lastErrorMessage]);

FMResultSet *rs = [database executeQuery:@"select Date_Time from scream_detections;"];
NSAssert(success, @"Unable to select data: %@", [database lastErrorMessage]);

while ([rs next]) {
    NSLog(@"date = %@", [rs dateForColumnIndex:0]);
}

[rs close];

and, from sqlite3 command line utility:

Robs-MacBook-Pro:Documents rryan$ sqlite3 test.sqlite 
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> SELECT datetime(Date_Time, 'unixepoch') FROM scream_detections;
2016-09-06 19:16:26

Perhaps you can share a reproducible example of your problem?