SQL Server – Converting Bigint to Date format

Posted onAuthorHeelpBook

SCENARIO

Hi all, we have an application from a third party vendor. In which a table stored time as below in bigint format.

CREATEDTIME RESPONDEDTIME DUEBYTIME
-------------------- -------------------- --------------------
1236666082555 1236666085435 1236680482555

And it is diplaying as below through their application

Created date : 10 Mar 2009 14:21:22
Responded Date : 10 Mar 2009, 10:21:25
Due date : 10 Mar 2009 14:21:22

Any idea how they are converting into this format?

SOLUTION

Those numbers are UNIX EPOCH numbers with milliseconds as the lowest three digits. In other words, 1236666082555 actually represents 1236666082 (number of seconds since midnight jan 1 1970), with an additional 555/1000 seconds. By the way, 1236666082 is 2009-03-10 06:21:22 UTC.

SELECT
DATEADD(ms, CREATEDTIME-(CREATEDTIME/1000)*1000,
DATEADD(ss, CREATEDTIME/1000+8*60*60, '19700101'))
FROM yourtable

NOTE: You’ll need to specify “yourtable” and the right fields in BIGINT date format (in this example this is CREATEDTIME).

SOURCE

LINK (DBforums.com)

LANGUAGE
ENGLISH