CODEWITHSUNDEEP
swiftsqlitedateinsert
Dharma
Dharma

Reputation: 81

Swift SQLite database how to insert date field

I have created Sqlite tables and I am trying to insert data. While inserting date field the compiler gives error:

Cannot convert value of type 'Date?' to expected argument type 'UnsafePointer!'

Here is my code:

func insert() {
    let db = openDatabase()
    var insertStatement: OpaquePointer? = nil

    if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
        let trNumber: String = scanOutput
        let empId: String = "TBD"
        let carrier = "TBD_CARRIER"
        let recLoc: String? = "TBD_LOCATION"
        let recDate: Date? = scannedDate
        let recdBy: String = "TBD_BUY"
        let dlyStatus: String? = "R"

        sqlite3_bind_text(insertStatement, 1, trNumber, nil)
        sqlite3_bind_text(insertStatement, 2, empId, -1, nil)
        sqlite3_bind_text(insertStatement, 3, carrier, -1, nil)
        sqlite3_bind_text(insertStatement, 4, recLoc, -1, nil)
        sqlite3_bind_text(insertStatement, 5, recDate, -1, nil) // This line gives error
        sqlite3_bind_text(insertStatement, 6, recdBy, -1, nil)
        sqlite3_bind_text(insertStatement, 7, dlyStatus, -1, nil)

// the code continues, but I have just reproduced the line where I get error

Upvotes: 1

Views: 4609

Answers (2)

Nick08
Nick08

Reputation: 167

Use the sqlite function datetime() in your insert statement

let insertStatementString = "INSERT INTO Records (trNumber, empId, carrier, recLoc, recDate, recdBy, dlyStatus) VALUES (?,?,?,?,datetime(?),?,?)"

Then convert your Date to a String and bind it as text

// Setup date (yyyy-MM-dd HH:mm:ss)
let formatter = DateFormatter()
formatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
let dateString = formatter.string(from: recDate)

// Bind values to insert statement
sqlite3_bind_text(insertStatement, 4, (dateString as NSString).utf8String, -1, nil)

For reference, see https://www.techonthenet.com/sqlite/functions/datetime.php

Upvotes: 1

rmaddy
rmaddy

Reputation: 318774

You are trying to sqlite3_bind_text with a Date but that is for strings.

I suggest you save the Date as follows:

if let recDate = recDate {
    sqlite3_bind_double(insertStatement, someColumnIndex, recDate.timeIntervalSinceReferenceDate)
} else {
    sqlite3_bind_null(insertStatement, someColumnIndex)
}

Then you can read it back with:

if sqlite3_column_type(queryStatement, someColumnIndex) != SQLITE_NULL {
    date = Date(timeIntervalSinceReferenceDate: sqlite3_column_double(queryStatement, someColumnIndex))
}

Upvotes: 2

Related Questions