T-Sql date format for seconds since last epoch / formatting for sqlite input
I'm guessing it needs to be something like:
CONVERT(CHAR(24), lastModified, 101)
However I'm not sure of the right value for the third parameter.
Well I'm trying to write a script to copy my sql server db to a sqlite file, which gets downloaded to an air app, which then syncs the data to another sqlite file. I'm having a ton of trouble with dates. If I select a date in air and try to insert it, it fails because it's not in the right format... even if it was a valid date to begin with. I figured I'd try to experiment with the unix time since that's the only thing thats worked so far. I am considering just leaving them as varchar because I don't sort by them anyway.
Last epoch is when 1970 GMT?
SELECT DATEDIFF(s,'19700101 05:00:00:000',lastModified)
See also Epoch Date
sqlite> select datetime(); 2011-01-27 19:32:57
sqlite> select strftime('%Y-%m-%d %H:%M:%S','now'); 2011-01-27 19:33:57
REFERENCE: (Date time Functions)[http://sqlite.org/lang_datefunc.html]