Comment créer des fonctions de début et de fin D'heure D'été dans SQL Server
j'ai besoin de créer une fonction dans le serveur SQL qui renvoie l'heure avancée de début de datetime et l'heure avancée de fin de datetime.
j'ai rencontré quelques exemples sur le web, mais ils utilisent tous la première date de Mars et la première date de novembre et ce n'est pas techniquement correct.
L'heure avancée commence à 2h le deuxième dimanche de Mars et se termine à 2h le premier dimanche de novembre.
j'ai commencé avec le code ci-dessous mais je suis sûr que c'est faux. Toute aide est très appréciée! :)
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
4 réponses
n'oubliez pas que les horaires de passage à l'heure d'été changent selon les pays, et sont également sujets à changement au fil des ans: le système américain actuel est entré en vigueur en 2007, par exemple.
en supposant que vous voulez le système actuel pour les États-Unis, voici une forme de réponse pour une année donnée.
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
ou comme des fonctions, mais vous devez savoir que DateFirst est fixé à 7, autrement les mathématiques.
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
personnellement, je pense qu'il est plus facile de trouver le premier dimanche de novembre que le deuxième dimanche de Mars. Heureusement, si vous en trouvez un, vous pouvez trouver l'autre parce qu'il y a toujours 238 jours entre eux. Voici donc une fonction pratique pour trouver la fin de 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;
maintenant, le début de Dst est la même fonction, seulement 238 jours plus tôt.
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 version 2016 va résoudre ce problème une fois pour toutes . Pour les versions précédentes, une solution CLR est probablement la plus facile. Ou pour une règle DST spécifique (comme nous seulement), une fonction T-SQL peut être relativement simple.
cependant, je pense qu'une solution générique T-SQL pourrait être possible. Aussi longtemps que xp_regread fonctionne, essayez ceci:
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
(complexe) de T-SQL fonction pourrait utiliser données déterminer le décalage exact pour toutes les dates de la règle actuelle de L'heure avancée.
Je n'étais pas vraiment satisfait de l'une des solutions que j'ai trouvées en ligne pour convertir UTC en heure locale, donc j'ai inventé cette fonction. Avoir un coup d'oeil à mon DONC la réponse ici
il y a une certaine logique là-dedans qui calcule si l'heure d'été est active en se basant sur la gamme de date standard utilisations de L'heure D'hiver (deuxième dimanche en Mars à 2h, horloges aller de l'avant; 1er dimanche en novembre revenir à l'heure normale)