Solved

Convert bigint to datetime

Posted on 2015-01-14
6
110 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 65

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 10

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

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)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 55
Job - date manual 1 42
TSQL recursive CTE challenge... 8 34
tempdb log keep growing 7 45
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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