--> (Word) | --> (PDF) | --> (Epub) | --> (Text) |
--> (XML) | --> (OpenOffice) | --> (XPS) | |
This article has been published [fromdate] |
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 |