There are a couple of good answers to a similar question asked on Stack Overflow. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant.
It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had some dates from the 1940s that still didn't convert properly. We ultimately needed the
UTC dates in our SQL Server database to line up with an algorithm in a 3rd party program that used the Java API to convert from
UTC to local time.
I like the
CLR example in Kevin Feasel's answer above using Harsh Chawla's example, and I'd also like to compare it to a solution that uses Java, since our front end uses Java to do the
UTC to local time conversion.
Wikipedia mentions 8 different constitutional amendments that involve time zone adjustments prior to 1987, and many of those are very localized to different states, so there is a chance that the CLR and Java may interpret them differently. Does your front-end application code use dotnet or Java, or are dates before 1987 an issue for you?