Solved

# BIGINT to DATETIME to GMT, or just BIGINT to GMT

Posted on 2013-05-15
1,575 Views
@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
AS
BEGIN
DECLARE @GMTDatetime datetime2
select @GMTDatetime =
CASE
BETWEEN
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)
AND
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)
END
RETURN @GMTDatetime
END
GO
0
Question by:dbaSQL

LVL 25

Assisted Solution

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

0

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?
0

LVL 75

Expert Comment

ID: 39167796
If that is CST and you want GMT, then something like this::
0

LVL 92

Assisted Solution

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.

:)
0

LVL 75

Expert Comment

ID: 39168188
Too true.  You are absolutely right.
0

LVL 17

Author Comment

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

LVL 17

Author Closing Comment

ID: 39170175
You're not missing anything, ac.  i was overthinking it.

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

## Featured Post

Question has a verified solution.

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