sql - 计算日期是否在 BST 内 - SQL Server

Question

我正在构建一个日期维度，我需要做的最后一部分是填充一个标志，说明该日期是否属于英国夏令时。英国夏令时从三月的最后一个星期日开始，到十月的最后一个星期日结束。到目前为止，我得到了这个:

update [Dim_Date_Test_BST]
set IsBST = 1
where (CalenderMonth BETWEEN 03 and 10)

我还有以下列出每个月最后一个星期日的代码:

dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,Date),-1))/7)*7,'17530107')

我正在努力将这两段代码组合起来以产生我需要的结果。有任何想法吗？谢谢。

修复，感谢以下贡献者:

SELECT DISTINCT
        CalenderYear = D.CalenderYear,
        BritishSummerStartDate = 
        CASE 
            WHEN D.CalenderMonth = 3 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') 
        END,
        BritishSummerEndDate = CASE 
            WHEN D.CalenderMonth = 10 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') 
        END
INTO #BST
FROM [dbo].[Dim_Date_Test_BST] AS D
WHERE D.CalenderMonth IN (3, 10)


update D 
SET IsBST = 1
FROM[dbo].[Dim_Date_Test_BST] AS D
INNER JOIN #BST a ON d.CalenderYear = a.CalenderYear AND a.BritishSummerStartDate IS NOT NULL
INNER JOIN #BST b ON d.CalenderYear = b.CalenderYear AND b.BritishSummerEndDate IS NOT NULL
WHERE D.Date BETWEEN a.BritishSummerStartDate AND b.BritishSummerEndDate

Answer 1

您可以尝试在每年生成一个具有正确开始/结束时间的集合，然后将其用作带有 BETWEEN 的引用。

;WITH BritishSummerPeriodsByYear AS
(
    SELECT DISTINCT
        CalenderYear = D.CalenderYear,
        BritishSummerStartDate = CASE 
            WHEN D.CalenderMonth = 3 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') END,
        BritishSummerEndDate = CASE 
            WHEN D.CalenderMonth = 10 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') END
    FROM
        [Dim_Date_Test_BST] AS D
    WHERE
        D.CalenderMonth IN (3, 10)
)
update D SET
    IsBST = 1
FROM
    Dim_Date_Test_BST AS D
    INNER JOIN BritishSummerPeriodsByYear AS BP ON D.CalenderYear = BP.CalenderYear
WHERE
    D.Date BETWEEN BP.BritishSummerStartDate AND BP.BritishSummerEndDate