如何在 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.

夏令时从 3 月第二个星期日凌晨 2 点开始，到 11 月第一个星期日凌晨 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屋！