Posted on 2013-05-15
Last Modified: 2013-05-16
@Time is BIGINT.  I need to convert to DATETIME2, but GMT.  I am in CST.

SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())
Returns:  1368617884
(i tried DATEDIFF(mcs, because we need microseconds format on the BIGINT, but it failed with overflow, saying it was too large.)

DECLARE @Time BIGINT = 1368617884
SELECT dbo.fn_bigintDatetime (@Time)

Result:  1970-01-16 20:10:18.0000000

Of course, my date should be 2013-05-15.....

Does anyone see my error?

CREATE FUNCTION [dbo].[fn_bigintDateTime]
(@Timestamp bigint)
RETURNS datetime2
       DECLARE @GMTDatetime datetime2
       select @GMTDatetime =
       WHEN dateadd(ss, @Timestamp/1000, '1970-01-01')
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @Timestamp/1000, '1970-01-01') )) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @Timestamp/1000, '1970-01-01') )/4 + 4) % 7)) + ' 01:00:00', 20)
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @Timestamp/1000, '1970-01-01') )) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @Timestamp/1000, '1970-01-01') )/4 + 1) % 7)) + ' 02:00:00', 20)
       THEN Dateadd(hh, 1, dateadd(ss, @Timestamp/1000, '1970-01-01'))
       ELSE Dateadd(hh, 0, dateadd(ss, @Timestamp/1000, '1970-01-01'))
RETURN @GMTDatetime    
Question by:dbaSQL
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
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 50 total points
ID: 39167735
So you have a bigint and you want to return the date?

This page has helped me in the past.
LVL 75

Accepted Solution

Anthony Perkins earned 400 total points
ID: 39167786
I am confused.  You want to go from seconds to datetime2, why not simply:
SELECT CAST(DATEADD(SECOND, 1368617884, '19700101') as datetime2)

What am I missing?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39167796
If that is CST and you want GMT, then something like this::
SELECT CAST(DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), DATEADD(SECOND, 1368617884, '19700101')) AS datetime2)
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 39168114
Word of caution about using the delta between GETDATE and GETUTCDATE...

It will make adjustments for current dates/times, but cannot be relied upon to always yield the right adjustment.

For example, if you use that delta to convert a datetime value from, say, February, but you are making the calculation today, you might be off by an hour because of Daylight Saving Time.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 39168188
Too true.  You are absolutely right.
LVL 17

Author Comment

ID: 39168803
sorry, guys.  insanity.  i will be back soon.
LVL 17

Author Closing Comment

ID: 39170175
You're not missing anything, ac.  i was overthinking it.
this works fine:  DATEADD(s,(@Time/1000),'1/1/1970')

Very good point, matthewspatrick .  And that is definitely something i am going to have to be wary of.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Http Post send by Store Procedure Help 5 31
T-SQL: Only Wanting One Record 8 61
Create a Calendar table 29 44
T-SQL: Wrong Result 7 39
In this article I will describe the Copy Database Wizard 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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