I came across a database that stores the execution date in UTC time. I needed to run a report with a sliding window to report errors during the day. Because this server was in Virginia, it is subject to daylight savings so I needed to be able to calculate the local time based off the UTC time and this could be 4 or 5 hours depending on the season.
I found the function GETUTCDATE as an easy way to calculate the UTC date. It should be noted that it uses the server time to calculate this so if you have multiple servers in different time zones there may be different logic needed for each one. In my case, I just needed to find the hour difference between my timezone and UTC–in this case I use the DATEDIFF function.
Now that I know how far away from UTC I am, I can apply local time logic to get the data I want. This example uses AdventureWorks table.
[sourcecode language=”SQL”]DECLARE @HourOffset INT
SET @HourOffset = DATEDIFF(hh,GETUTCDATE(),GETDATE())
SELECT DATEADD(hh,@HourOffset,ErrorTime) AS ExecutionTime