Solved

BIGINT to DATETIME to GMT, or just BIGINT to GMT

Posted on 2013-05-15
7
1,547 Views
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
AS
BEGIN
       DECLARE @GMTDatetime datetime2
       select @GMTDatetime =
       CASE
       WHEN dateadd(ss, @Timestamp/1000, '1970-01-01')
       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)
       THEN Dateadd(hh, 1, dateadd(ss, @Timestamp/1000, '1970-01-01'))
       ELSE Dateadd(hh, 0, dateadd(ss, @Timestamp/1000, '1970-01-01'))
       END
RETURN @GMTDatetime    
END
GO
0
Comment
Question by:dbaSQL
7 Comments
 
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.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b4b8cba3-f4c8-40d7-960a-ce13467f09a8
0
 
LVL 75

Accepted Solution

by:
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

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)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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.

:)
0
 
LVL 75

Expert Comment

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

Author Comment

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

Author Closing Comment

by:dbaSQL
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now