Convert SQL datetime from UTC to local
Posted on 2009-04-23
I have a MS SQL database in which all datetimes are stored as UTC(GMT). All work that this database keeps track of is in the EST time zone. I need to make a stored procedure that can convert a given datetime to whatever the local time should be. I've figured out how to do this with an offset and the DATEADD() function, but I dont know how to determine the appropriate offset for the datetime. I could use this:
but this calculates the offset based on the current time, which fails in the case where I am calculating a datetime from daylight savings at a time that isnt in daylight savings.
It seems like if I could seed GETDATE and GETUTCDATE with the date I am working with that would work, but I dont know how or if I can do that.