CODEWITHSUNDEEP
sqlms-accesstimestampms-access-2010
JBurace
JBurace

Reputation: 5633

Convert timestamp to date/time?

I have a number field with data like:

875612015
644936666
644936071
644939568
673358599
665166803

These should be date/times, how can I convert these to readable date/times in SQL (select statement)?

To reverse engineer some data points:

                 |
11/10/10   1219 = 968674746
11/12/10   1033 = 968841218
11/12/10   1302 = 968850160
11/12/10   1332 = 968851974
11/16/10   1212 = 969192756
11/16/10   1526 = 969204361
07/19/12   1036 = 1021977398

These time values are likely stored in something related to MUMPS

Upvotes: 0

Views: 1002

Answers (1)

PaulStock
PaulStock

Reputation: 11263

Try this:

SELECT DateAdd ( "s", DATENUMBERFIELD, #01/01/1970# )
FROM SourceDatabase

UPDATE:

OK, based on your examples, looks like the base date is 03/01/1980, not 1/1/1970 so try this:

SELECT DateAdd ( "s", DATENUMBERFIELD, #03/01/1980# )
FROM SourceDatabase

Upvotes: 3

Related Questions