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

x
?
Solved

BIGINT to DATETIME to GMT, or just BIGINT to GMT

Posted on 2013-05-15
7
Medium Priority
?
1,671 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
[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
7 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 200 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 1600 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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

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.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

715 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