Trying to convert UTC datetime to local datetime for a view.
The dates go 3/4 years back so i am trying to find a solution thats works for dates in the past to.
The diffrence should be 1 or 2 hours depending on the part of the year.
The image below shows one of the solutions i tried but it is not working because it will always add 2 hours (until our clock changes back to 1 hour diffrence).
,[Timestamp] AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS LocalTime
@harishgg1 , AT TIME ZONE is not available in SQL Server 2012.
@Jordy3003 , I'd recommend a calendar table, Aaron Bertrand has a few examples:
There's a link to an earlier article of his in that one too, you should read both. You can easily modify the calendar to include "IsDST" for daylight savings, or even better, "DST_Offset_Minutes", and include the number of minutes to add to the calculation:
DATEADD(minute,DATEDIFF(minute,GETUTCDATE(), GETDATE())+DST_Offset_Minutes, [Measured])
This is assuming you can't upgrade your SQL Server instance to 2016 or higher (and 2016 is out of support, so 2019 or 2022 is preferred)
Thank you Robert
For pointing it out
mssqltips has a great article about how to make time zone table:
handle-conversion-between-time-zones-in-sql-server-part-1
handle-conversion-between-time-zones-in-sql-server-part-2
handle-conversion-between-time-zones-in-sql-server-part-3
Cant place links.
Moderator: edited to include links: