Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

BIGINT to DATETIME to GMT, or just BIGINT to GMT

Posted on 2013-05-15
7
Medium Priority
?
1,693 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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 ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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