• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Convert bigint to datetime

I have a value from a vendor sql table that stores date as a big int

I'm assuming the value is in seconds from beginning of time

Looking to write a function that will convert to date time but not sure how

Also, we are in central time zone with daylight savings time

Using sql server 2008 r2
0
johnnyg123
Asked:
johnnyg123
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a data mockup of what this bigint looks like, e.g. January 1, 2015 = 20150114 ?
0
 
SimonCommented:
>I'm assuming the value is in seconds from beginning of time
You'll need to know the epoch date that they're using to represent the 'beginning of time'

e.g.  January 1, 1970 (the standard *nix epoch date)
select dateadd( SECOND, 1354320000, '19700101' )
0
 
Scott PletcherSenior DBACommented:
I'd use 86,400 secs per day to break it up into sizes that DATEADD can handle:

SELECT DATEADD(SECOND, big_time / 86400, DATEADD(DAY, big_time / 86400, '19000101')) AS final_datetime
FROM (
    SELECT CAST(3630182400+61200+2357 AS bigint) AS big_time
) AS test_data

Hopefully you can also use that to work your back to the "base" date being used.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
HuaMinChenBusiness AnalystCommented:
Hi,
Here is an example
select dateadd(second, 1334034120644 /1000 + 8*60*60, '19700101')

Open in new window


to transfer bigint to datetime.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Hello!

Years ago, I wrote a small piece on converting the EPOCH date/UNIX timestamp to a date/time value using the SQL Server DATEADD function. You can read about it at: http://beyondrelational.com/modules/2/blogs/77/posts/11277/converting-unix-timestamp-bigint-to-datetime-value-using-dateadd-function.aspx

The key point to understand here is that DATEADD works with INT (32-bit) values whereas the EPOCH time is a BIGINT (64-bit). Hence, some basic conversion is required before we can use DATEADD on it (as shown in my post). The 32-bit INT is limited to addressing up to January 19, 2038 (which leads us to the Year 2038 problem: http://beyondrelational.com/modules/2/blogs/77/posts/11278/unix-timestamp-and-the-year-2038-problem.aspx)
0
 
Scott PletcherSenior DBACommented:
CORRECTION:

"SELECT DATEADD(SECOND, big_time / 86400, DATEADD(DAY, big_time / 86400, '19000101')) "

should be                                                 *

SELECT DATEADD(SECOND, big_time % 86400, DATEADD(DAY, big_time / 86400, '19000101'))
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now