Get local datetime from UTC time

Question

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

Answer 1

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

---

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