HEELPBOOK - SQL Server - Converting Bigint to Date format #####################


############ 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).

############ ARTICLE INFO #############

Article Month: March
Article Date: 08/03/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-converting-bigint-to-date-format/
Source: http://www.dbforums.com/microsoft-sql-server/1640233-converting-bigint-date-format.html
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook