Как создать летнее время Функция запуска и завершения в SQL Server

Мне нужно создать функцию на SQL-сервере, которая возвращает летнее время, начиная с даты и времени, а также с летнего времени, заканчивающегося datetime.

Я наткнулся на несколько примеров в Интернете, однако все они используют 1-ю дату марта и 1-ю дату ноября, и это не технически правильно.

Переход на летнее время начинается в 2 часа ночи во второе воскресенье марта и заканчивается в 2 часа ночи в первое воскресенье ноября.

Я начал с кода ниже, но я уверен, что это неправильно. Любая помощь приветствуется! 🙂

DECLARE @DSTSTART DATETIME SELECT @DSTSTART = CASE WHEN DATEPART(MONTH, SYSDATETIME()) = 3 AND DATEPART(weekday, SYSDATETIME()) = 1 AND DATEDIFF(week,dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, SYSDATETIME()), 0)), 0), SYSDATETIME() - 1) = 2 AND DATEPART(HOUR, SYSDATETIME()) = 2 THEN SYSDATETIME() END RETURN (@DSTSTART) END GO 

Не забывайте, что графики перехода на летнее время меняются в зависимости от страны, а также могут меняться с годами: например, в 2007 году действует действующая система США.

Предполагая, что вам нужна нынешняя система для США, вот одна из форм ответа на любой год.

 SET DATEFIRST 7 DECLARE @year INT = 2013 DECLARE @StartOfMarch DATETIME , @StartOfNovember DATETIME , @DstStart DATETIME , @DstEnd DATETIME SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0)) SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0)); SET @DstStart = DATEADD(HOUR, 2, DATEADD(day, ( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 ) + 7, @StartOfMarch)) SET @DstEnd = DATEADD(HOUR, 2, DATEADD(day, ( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ), @StartOfNovember)) SELECT @DstStart AS DstStartInUS , @DstEnd AS DstEndInUS 

или как функции, но вы должны знать, что DateFirst установлен в 7, иначе математика будет отключена.

 CREATE FUNCTION GetDstStart ( @Year AS INT ) RETURNS DATETIME AS BEGIN DECLARE @StartOfMarch DATETIME , @DstStart DATETIME SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0)) SET @DstStart = DATEADD(HOUR, 2, DATEADD(day, ( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 ) + 7, @StartOfMarch)) RETURN @DstStart END GO; CREATE FUNCTION GetDstEnd ( @Year AS INT ) RETURNS DATETIME AS BEGIN DECLARE @StartOfNovember DATETIME , @DstEnd DATETIME SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0)) SET @DstEnd = DATEADD(HOUR, 2, DATEADD(day, ( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ), @StartOfNovember)) RETURN @DstEnd END 

Лично я считаю, что легче найти первое воскресенье ноября, чем найти второе воскресенье марта. К счастью, если вы его найдете, вы можете найти другое, потому что между ними всегда 238 дней. Итак, вот удобная функция, чтобы найти конец Dst:

 create function GetDstEnd ( @Year int ) returns datetime as begin declare @DstEnd datetime; ;with FirstWeekOfNovember as ( select top(7) cast(@Year as char(4)) + '-11-0' + cast(row_number() over(order by object_id) as char(1)) + ' 02:00:00' 'DST_Stops' from sys.columns ) select @DstEnd = DST_Stops from FirstWeekOfNovember where datepart(weekday,DST_Stops) = 1 return @DstEnd; end; 

Теперь начало Dst является той же функцией, всего 238 дней назад.

 create function GetDstStart ( @Year int ) returns datetime as begin; declare @DstStart datetime; ;with FirstWeekOfNovember as ( select top(7) cast(@Year as char(4)) + '-11-0' + cast(row_number() over(order by object_id) as char(1)) + ' 02:00:00' 'DST_Stops' from sys.columns ) select @DstStart = dateadd(day,-238,DST_Stops) from FirstWeekOfNovember where datepart(weekday,DST_Stops) = 1 return @DstStart; end; go 

SQL Server версии 2016 решит эту проблему раз и навсегда . Для более ранних версий решение CLR, вероятно, проще всего. Или для определенного правила DST (например, только для США) функция T-SQL может быть относительно простой.

Однако, я думаю, что может возникнуть общее решение T-SQL. Пока работает xp_regread , попробуйте следующее:

 CREATE TABLE #tztable (Value varchar(50), Data binary(56)); DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation' EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT; SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname INSERT INTO #tztable EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI'; SELECT -- See http://msdn.microsoft.com/ms725481 CAST(CAST(REVERSE(SUBSTRING(Data, 1, 4)) AS binary(4)) AS int) AS BiasMinutes, -- UTC = local + bias: > 0 in US, < 0 in Europe! CAST(CAST(REVERSE(SUBSTRING(Data, 5, 4)) AS binary(4)) AS int) AS ExtraBias_Std, -- 0 for most timezones CAST(CAST(REVERSE(SUBSTRING(Data, 9, 4)) AS binary(4)) AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier -- When DST ends: CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear, -- 0 = yearly (else once) CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth, -- 0 = no DST CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek, -- 0 = Sunday to 6 = Saturday CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek, -- 1 to 4, or 5 = last <DayOfWeek> of <Month> CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour, -- Local time CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute, CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond, CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec, -- When DST starts: CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear, -- See above CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth, CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek, CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek, CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour, CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute, CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond, CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec FROM #tztable; DROP TABLE #tztable 

Функция A (complex) T-SQL может использовать эти данные для определения точного смещения для всех дат в течение текущего правила DST.