T-Sql date format for seconds since last epoch / formatting for sqlite input

Question

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.

Thanks!

Answer 1

Define "last epoch". Does this come close?

Select Cast(lastModified As Integer)

Answer 2

Last epoch is when 1970 GMT?

SELECT DATEDIFF(s,'19700101 05:00:00:000',lastModified)

See also Epoch Date

Answer 3

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.

Answer 4

If you store them as varchar, store them as YYYYMMDD. That way you CAN sort by them later if you want to.

Answer 5

SQL server has only 2 failsafe date formats

ISO = YYYYMMDD, run this to see that

  select convert(varchar(10),getdate(),112)

ISO8601 = yyyy-mm-dd Thh:mm:ss:mmm(no spaces) run this to see that

select convert(varchar(30),getdate(),126)

To learn more about how dates are stored in SQL server I wrote How Are Dates Stored In SQL Server?

Answer 6

I wound up using format 120 in MS SQL:

convert(char(24), lastModified, 120)

Each time I needed to a select a date in SQLite for non-display purposes I used:

strftime(\"%Y-%m-%d %H:%M:%S\", dateModified) as dateModified

Now I just need a readable/friendly way to display the date to the user!

edit: accept answer goes to whoever shows me how to display the date nicely from sqlite ;p