I would like to convert my UTC datetime column (filled with historical dates) into local time but automatically taking into account DST.
I know that's possible to add one or two hours to the current time, but then I need to find out when the shift occurs. (for every year)
Is it possible to convert UTC time to local time and automatically taking into account DST based on the date to convert ?
Using SQL Server 2008 R2
How hard or how easy it is depends on a) how far back you want to do the historical data conversions, and b) how many countries/regions you want to do the conversions for.
Simplifying it one step and assuming that you want to convert UTC to US Eastern Time, the best way to do it would be to store the dates on which the daylight savings time changed into a table and use that table to decide how many hours to subtract to get the US Eastern time. The history of daylight savings time dates are available on the web - for example here.
Note a few things if your requirements are any more complicated than that:
a) Even in the USA, some states (Arizona) and some regions (parts of Indiana) do not observe daylight savings time.
b) The schedule of when the daylight savings time takes effect has changed historically. Currently it is the first Sunday of March (or something like that), but that was put in effect some ten years ago.
c) The dates on which daylight savings time takes effect changes from country to country, so if you had to make your queries work for multiple regions, you would need a table with the region info and the dates of daylight savings time.
It's over the past 4 years and just one country. (from UTC to CEST).
if I understand you correctly, there is no standard function?
There is no standard function in T-SQL to convert UTC to local time historically. For current values - for example today's datetime values, you could do something like in the following example.
DECLARE @utcdate DATETIME = '2015-05-22 11:11:37.127'
SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),@utcdate) AS currentLocalTime;
This of course, assumes that your server is on local time.
Ok. Many thanks for your help.