4
\$\begingroup\$

One of the app is inserting date in UTC format (column CreateDate). When reporting from this table, I used a difference in hours between the inbuilt GetUTCDate() and GetDate() and added the same to the CreateDate column. I was hoping to not worry about the daylight time. Is this approach OK?

SELECT GetUTCDate()
SELECT GetDate()
DECLARE @DifferenceInHoursBetween INT
SELECT @DifferenceInHoursBetween = DATEDIFF(HH,GetUTCDate(), GETDATE())
SELECT  USER_ID, CreateDate AS [DateTime IN UTC], DATEADD(HOUR,@DifferenceInHoursBetween,CreateDate) AS [LocalDateTime] FROM EVENTLOG(nolock)

/Tests/

SELECT GetUTCDate() AS [UTCDateTime]
SELECT GetDate() AS [LocalDateTime]
DECLARE @DifferenceInHoursBetween INT
SELECT @DifferenceInHoursBetween = DATEDIFF(HH,GetUTCDate(), GETDATE())
SELECT @DifferenceInHoursBetween AS [Difference in Hours]

-- UTCDateTime

2019-01-22 16:34:46.943

--LocalDateTime

2019-01-22 08:34:46.943

-- Difference in hours -8

\$\endgroup\$
  • 1
    \$\begingroup\$ good approach is to save the date in UTC \$\endgroup\$ – Dmitrij Kultasev Jan 23 at 13:32
  • \$\begingroup\$ To be fair, if DST was your initial concern, your SQL is not working as intended. \$\endgroup\$ – dfhwze Jul 27 at 18:20
1
\$\begingroup\$

Review

You don't need to calculate the difference from two temporary values GetUTCDate and GETDATE.

SELECT @DifferenceInHoursBetween = DATEDIFF(HH,GetUTCDate(), GETDATE())

Proposed Solution

Instead, you could apply the local time zone offset to the specified datetime.

SELECT  
    USER_ID, 
    CreateDate AS [DateTime IN UTC], 
    CONVERT(datetime, 
        SWITCHOFFSET(CONVERT(datetimeoffset, CreateDate), 
            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [LocalDateTime] 
FROM EVENTLOG(nolock);

Fiddle: SQL Server 2017

enter image description here


Daylight Savings Time

Both methods, OP and solution don't take into account DST. And unfortunately, SQL Server does not come with a built-in conversion from DST. You'd have to make a function yourself. More about DST in SQL Server

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.