Get local datetime from UTC time
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