SET = DATEADD(HOUR, answer contains an error. RETURNS AS AS AS AS AS AS AS AS AS AS AS AS = CONVERT(TIME, DATEADD(HOUR, ' = CONVERT(TIME, DATEADD(HOUR, - 1, ' = CONVERT(CHAR(16), + '-03-01 ' + CONVERT(CHAR(5), = CONVERT(CHAR(16), + '-11-01 ' + CONVERT(CHAR(5), = DATEPART(WEEKDAY, = DATEPART(WEEKDAY, number of days between Mar 1 and DST start dateĮLSE SET = 15 - number of days between Nov 1 and DST end dateĮLSE SET = 8 - DST start and end = DATEADD(DAY, = DATEADD(DAY, UTC offset if is in DST Range SELECT dbo.udfToLocalTime(' 8:00', DEFAULT) SELECT dbo.udfToLocalTime(' 10:00', DEFAULT) SELECT dbo.udfToLocalTime(' 9:00', DEFAULT) Function assumes that the 1st day of the week is Sunday. Function doesn't apply for years prior to 2007. Good only for US States observing the Energy Policy Act of 2005. Notes: Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well. Purpose: To convert UTC to local US time accounting for DST Here's a version that accounts for daylight savings, UTC offset, and is not locked into a particular year. Note you can not just return AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'" as the result because this result is actually a UTC time in EST format (when you compare this "fake" EST time or include it in an order clause it will be converted back to a UTC time). Select dbo.fnConvertUTCTimetoESTTime (' 4:00:00.000') Select dbo.fnConvertUTCTimetoESTTime (' 5:00:00.000') Return convert(datetime, convert(varchar(23), AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 121), 121) You can change your designed time zone name in this function, or get it from registry: Create Function as datetime This function will convert a UTC time to EST time with DST adjustment. So this is a T-SQL (SQL Server Answer), no need for storedproc of functions. Select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring, receiveTimeUTC , CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, receiveTimeUTC), DATENAME(TzOffset, receiveTimeUTC AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST , CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, receiveTimeUTC), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring In below data, 2 queries, one on feb data (DST is off, winter in Amsterdam) +1 diffĪnd 2nd query on april data in Amsterdam, so +2 hour diff. Take your own 'AT TIME ZONE' to capture the TzOffset for your column/data field, and not the current SYSDATETIME. Return easiest answer is not always at the bottom, but this time it is, and can be seen already somewhere hidden in above comments. RETURNS datetime, - Second Sunday in datetime - First Sunday in November If you need a conversion other than your server's location, here is a function that allows you to pass a standard offset and accounts for US Daylight Savings Times: - =
0 Comments
Leave a Reply. |