Convert SQL DateTime to UTC
If you google 'Convert SQL DateTime to UTC' the top result is something like:
DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), OrigTime) as NewTime
BUT THIS IS NOT 100% reliable. There seems to be about a 1 in 20 chance you'll get a time either + or - one second.
I don't know if the following is a guaranteed fix, but switching formulas to something more precise, the issue went away:
DATEADD(second, DATEDIFF(second, SYSDATETIME(), SYSUTCDATETIME()), OrigTime) as NewTime
Note: this is a workaround for SQL2014 or less. SQL2016 has a proper conversion function. This also only works for dates falling in same daylight savings time period as 'now' (i.e. don't use on old dates)
Created By: amos 3/29/2019 5:40:45 PM