Use AT TIME ZONE to add offset based on time zone rules when you know that the original datetime values are provided in the same time zone: USE AdventureWorks2016 Add target time zone offset to datetime without offset information Since some information (such as timezone rules) is maintained outside of SQL Server and are subject to occasional change, the AT TIME ZONE function is classed as nondeterministic. Time after 03:00 is regularly presented with the standard time offset (+01:00) Time from the "overlapped interval" is presented with DST offset (before the change)
Time before the change has DST offset (+02:00) In that case, points in time that belong to the overlapped interval are presented with the offset before the clock change: /*Ĭhange occurred on October 25th, 2015 at 03:00:00. When the clock is set back, then 2 hours of local time are overlapped onto one hour. Time after 03:00 is presented with the summer time offset (+02:00) Is moved 1 hour ahead and presented with the summer time offset SELECT CONVERT(DATETIME2(0), 'T01:01:00', 126)ĪT TIME ZONE 'Central European Standard Time' Īdjusted time from the "gap interval" (between 02:00 and 03:00) Time before DST change has standard time offset (+01:00) Moving to DST in "Central European Standard Time" zone:Ĭhange occurred on March 29th, 2015 at 02:00:00. Points in time that are in this gap are converted with the offset after DST change. This duration is usually 1 hour, but it can be 30 or 45 minutes, depending on time zone. When the clock's set ahead, there's a gap in local time equal to the duration of the clock adjustment. RemarksĪT TIME ZONE applies specific rules for converting input values in smalldatetime, datetime, and datetime2 data types that fall into an interval affected by a DST change: The datetimeoffset value in the target time zone. A list of installed time zones is also exposed through the sys.time_zone_info (Transact-SQL) view. Time zones installed on the computer are stored in the following registry hive: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones.
#Etc utc time zone windows#
SQL Server relies on time zones that are stored in the Windows Registry. Is an expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value. Transact-SQL Syntax Conventions Syntax inputdate AT TIME ZONE timezone If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.ĪT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. Converts an inputdate to the corresponding datetimeoffset value in the target time zone.
0 kommentar(er)
