I always wanted to have a good personal diary application, but the freeware applications available do not satisfy my requirements. So I decided to write my own using AIR.

However, I haven’t really worked that much with DATE columns in SQLite. And had some problems in inserting dates and retrieving them from the db. So I want to share my research and solutions here.

This is the crucial extract from the documentation:

Explicit data typing

Parameters are used to allow for typed substitution of values that are unknown at the time the SQL statement is constructed. The use of parameters is the only way to guarantee the storage class for a value passed in to the database. When parameters are not used, the runtime attempts to convert all values from their text representation to a storage class based on the associated column’s type affinity.

What this means?

Supposed you have a SQL table which has a "DATE" column, then the right way to insert data is to use parameter substitution SQL prepared statements.

Correct way

var sql:String =
        "INSERT INTO entries (entryText, entryDate, summary,
          entryMood)
         VALUES ( :entryText, :entryDate, :summary,
          :entryMood)";

insertStmt.text = sql;                   
insertStmt.parameters[":entryText"] = inputTxt.text ;
insertStmt.parameters[":entryDate"] = inputDate.selectedDate;
insertStmt.parameters[":summary"] = inputSummary.text;
insertStmt.parameters[":entryMood"] = ‘happy’;

In the above code, "inputDate" is a DateField. Since we are using parameter passing, the Date ActionScript object is not converted to text which preserves the data. If we did not use parameters then the date will be translated into SQL TEXT type and this will be incorrect. So the following is the incorrect way of doing things.

Incorrect way

var sql:String =
          "INSERT INTO entries (entryText, entryDate, summary,
           entryMood)
          VALUES( ‘ +
          + input.text + "’," +
          + inputDate.selectedDate
…

You can also probably use another variation:

1. Set the data type of the date column in the SQL table to TEXT
2. Convert dates to string before inserting them into SQLite, by using the DateField.dateToString() function.
3. You can retrieve the date strings from the database and then use the DateField.stringToDate() to convert strings into date objects.
4. Take care that the format strings in step 2 and 3 should be same.

Also, note that using this method does not preserve the "time" in the date. Hope this helps.