Solved

Convert bigint to datetime

Posted on 2015-01-14
6
124 Views
Last Modified: 2015-01-29
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
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40550122
Give us a data mockup of what this bigint looks like, e.g. January 1, 2015 = 20150114 ?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40550126
>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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40550171
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40550453
Hi,
Here is an example
select dateadd(second, 1334034120644 /1000 + 8*60*60, '19700101')

Open in new window


to transfer bigint to datetime.
0
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 40551670
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40578183
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question