Air Sqlite Date in YYYY/MM/DD HH:MM Format

While I was playing with Air Sqlite, I got some trouble in saving Date in the Database

INSERT INTO tblUserComments (comment_text, comment_cat,comment_date,parent_id) VALUES('"+bubbleText.text+"','"+chosenCat+"',DATETIME('now', 'localtime'),'"+_parentId+"')

store the comment_date in the fol开发者_运维问答lowing format

2455783.2596296296 instead of 2011/08/09 18:13.

How can I save the Date Data as YYYY/MM/DD HH:MM?

Thank you so much.

After reading up on it, your column can't be set as DATE, since there isn't such a type in SQLite. To quote the dev manual:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

I would recommend you save the date as an INTEGER of epoc time, then you can do the conversion to whatever either when selecting the rows or within the code. I personally prefer to use the Date class to convert it properly depending on timezone. ie. var date:Date = new Date(epocTime);

use parameters instead because it regulates many issues automatically and set the type of your column in sqlite to DATE and assign it a new Date() object directly or assign it a string value in the following format:

private function dateToMySqlFormat(date:Date = null):String
{
    var formattedDate:DateFormatter = new DateFormatter();
    formattedDate.formatString = "YYYY-MM-DD JJ:NN:SS";

    if(date == null) date = new Date;

    return formattedDate.format(date);
}

that should work