timezone - Need a SQL Server function to convert local datetime to UTC that supports DST -

we migrating application azure, of our dates stored eastern standard time. since sql azure on utc , many of our dates generated getdate() call, we're going have issues if leave is. seems option provide least long term headache convert of our stored dates utc, , have them converted local time on front end.

unfortunately seems there isn't built-in way convert sql datetime 1 time zone another, , many of solutions i've found don't take daylight savings time consideration. solutions i've found consider dst involve importing calendars , time zone tables , extremely complex.

i'm looking work single conversion, , doesn't have pretty. ideas?

update: wrote following function believe accomplish need. see holes this? of dates eastern standard time, dates start around 2002 had handle law change in 2007.

i'm thinking use function this:

update mytable set mydate = dbo.fnesttoutc(mydate) 

here's function:

create function [dbo].[fnesttoutc]     (@pestdate  datetime)     returns datetime begin  declare @timezoneoffset int declare @year int declare @day int declare @dststart datetime declare @dstend datetime  select @year = datepart(year, @pestdate)  if @year >= 2007 begin     select @day = 8     while @dststart null     begin         -- second sunday in march         if datepart(weekday, datefromparts(@year, 3, @day)) = 1             select @dststart = datetimefromparts(@year, 3, @day, 2, 0, 0, 0)         select @day = @day + 1     end      select @day = 1     while @dstend null     begin         -- first sunday in november         if datepart(weekday, datefromparts(@year, 11, @day)) = 1             select @dstend = datetimefromparts(@year, 11, @day, 1, 0, 0, 0)         select @day = @day + 1     end end else begin     select @day = 1     while @dststart null     begin         -- first sunday in april         if datepart(weekday, datefromparts(@year, 4, @day)) = 1             select @dststart = datetimefromparts(@year, 4, @day, 2, 0, 0, 0)         select @day = @day + 1     end      select @day = 31     while @dstend null     begin         -- last sunday in october         if datepart(weekday, datefromparts(@year, 10, @day)) = 1             select @dstend = datetimefromparts(@year, 10, @day, 1, 0, 0, 0)         select @day = @day - 1     end end  if @pestdate >= @dststart , @pestdate < @dstend begin     -- date in dst     select @timezoneoffset = 4 end else begin     -- not dst     select @timezoneoffset = 5 end  return ( dateadd(hh, @timezoneoffset, @pestdate) ) end 

there several problems:

• you basing rule use on current date, rather on date provided in input. should change sure.

• if pass value such 2013-03-10 02:30, function assume edt, in reality time invalid , should not exist in data. should raise error.

• if pass value such 2013-11-03 01:30, function assume edt, in reality might have been in either edt or est. need have stored either offset or dst flag disambiguate. if it's not in data, have no choice assume 1 or other.

• this function doesn't account dates before 1987, had dst rule change in united states. if have data before then, should account well.

other that, looks fine. still, points in comments correct. work 1 time zone, , have no guarantees rules time zone won't change in future. recommend use convert data use utc going forward. use function conversion if like, or in application-level code.

oh, , 1 other thing. "eastern standard time" or "est" literally means utc-5 without regard daylight saving time @ all. "eastern daylight time" or "edt" means utc-4. assume meant data in "eastern time" in question, accommodates both.

if actually meant est, job lot simpler - add 5 hours , call done. bring because there indeed scenarios data recorded without respect daylight saving time. (i believe there use cases in financial sector work that.)