如何在SQL Server中创建夏令时开始和结束功能 [英] How to create Daylight Savings time Start and End function in SQL Server

问题描述

我需要在SQL Server中创建一个返回夏令时开始日期时间和夏令时结束日期时间的函数。

I need to create a function in SQL server that returns daylight savings time start datetime and daylight savings time end datetime.

我在上面遇到了一些示例在网络上，但是它们都使用3月1日和11月1日，并且在技术上不正确。

I've come across a few examples on the web, however they all are using the 1st date of March and the 1st date of November and thats not technically correct.

夏令时开始于第二个星期日的凌晨2点

Daylight savings time begins at 2AM on the 2nd Sunday of March and ends on at 2AM in the first Sunday in November.

我从下面的代码开始，但是我确定它是错误的。任何帮助表示赞赏！ ：）

I've started with the below code but I'm sure its wrong. Any assistance is appreciated! :)

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年生效。

Don't forget that daylight saving time schedules change depending on country, and also are subject to change over the years: the current US system took effect in 2007, for example.

假设您要使用美国的当前系统，这是任何给定年份的一种答案。

Assuming you want the current system for the US, here's one form of an answer for any given year.

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，否则数学将关闭。

or as functions, but you have to know that DateFirst is set to 7, otherwise the math will be off.

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

这篇关于如何在SQL Server中创建夏令时开始和结束功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！