Recently we needed to convert all dates in our database to UTC format. We've made a mistake not storing them in UTC from the beginning and it led to complicated issues. When you have to support various time zones, there is no other way but to store you dates in UTC.
Luckily enough SQL Server comes with a handy GETUTCDATE() function, which, as you all have guessed, returns the current UTC date. The only thing you need to know to convert your dates to UTC is the offset between your server's time and UTC. You can do it like this:
SELECT datediff(hour,GETUTCDATE(), getdate())
For Eastern Standard Time that will be -5. So to convert your dates to UTC you need to do this:
UPDATE Table SET Date = DATEADD(hour, -5, Date)
But there is one problem.
Currently the offset between UTC and EST is actually -4, since it's summer and we are now in daylight savings time (EDT). So the above code will substract 4 hours from every date we have. But obviously lots of those past dates are not during daylight savings and you need to substract full five hours from them. Turns out there is really no way around that in SQL Server.
But you can perform the converion outside of SQL. .NET has great built in tools to convert between time zones with daylight savings support. It is really easy to do the conversion in C#:
[SqlFunction] public DateTime Convert(DateTime date) { return TimeZone.CurrentTimeZone.ToUniversalTime(date); }
Then you just need to store it in the database and you are all set. You can even create a CLR stored procedure out of that method. Hopefully all of the above will save you some time.