This post has already been read 823 times!

SQL function for hour changes between UTC(Universal Time Coordinate) and  the France zone

Hour Change
CREATE FUNCTION dbo.F_CHANGEMENT_HEURE (@AN INT)
RETURNS @T TABLE (JOUR DATETIME, SAISON VARCHAR(5), DECALAGE_UTC SMALLINT)
AS
BEGIN
DECLARE @DD DATETIME;
-- passage à l'heure d'été, le dernier dimanche de mars à 1h
SET @DD = CAST(CAST(@AN AS CHAR(4)) + '0331 00:01' AS DATETIME);
WHILE DATEPART(weekday, @DD) <> 7
SET @DD = DATEADD(day, -1, @DD);
INSERT INTO @T VALUES (@DD, 'Hiver', +2);
-- passage à l'heure d'hiver, le dernier dimanche d'octobre à 1h
SET @DD = CAST(CAST(@AN AS CHAR(4)) + '1031 00:01' AS DATETIME);
WHILE DATEPART(weekday, @DD) <> 7
SET @DD = DATEADD(day, -1, @DD);
INSERT INTO @T VALUES (@DD, 'Été', +1);
RETURN;
END
GO
Use Case
SELECT *
FROM   dbo.F_CHANGEMENT_HEURE(2009)
JOUR                    SAISON DECALAGE_UTC
----------------------- ------ ------------
2009-03-29 00:01:00.000 Hiver  2
2009-10-25 00:01:00.000 Été    1

Leave a Reply

Post Navigation