SQLTeam.com | Weblogs | Forums

How to get UTC for past date


#1

I need to find UTC date for past date, i don't want to use GETUTCDATE() function so without using
dateAdd(hour,
datediff(hour, GETUTCDATE(), getdate()), --UTC offset
dateadd(second, Last_Test_Date, '1/1/1970 12:00 AM'))
need to find UTC for past date.


#2

I didn't quite understand your question, e.g., the relevance of '1/1/1970' - is your date stored as epoch time?

Regardless, if you have local datetime (including time portion) stored in a database table and you want to convert it to UTC date, you have to add the offset in effect for that particular date. If the local time is in a region of the world where they do not use daylight savings time, it is simple - the offset will be the same always.

In parts of the world that follow daylight savings time, your task is more complicated because the offset depends on whether you are in daylight savings time or not. The way to do it is to have the daylight savings start and end dates stored in a table and make use of that table.