Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert bigint to datetime

Posted on 2015-01-14
6
Medium Priority
?
164 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 14

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 70

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

688 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