如何在Sql server 中确定公共假期？

Question

我有一个用 c# 编写的应用程序，不能在公共假期或周末运行。我环顾四周，没有找到任何地方（官方）提供未来 50 年的所有公共假期。

如果我能得到这些，我只需将它们批量插入到我的 sql server 假期表中并使用它。但是，我无法在任何地方找到这些数据。

有谁知道 Sql server 是否支持公共假期或某种算法来解决它们？或者有没有人有官方可以批量插入的地方。

原文由 HAdes 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我接受了 Vonpota 的回答并将其设为表值函数，因此我可以使用 NOT IN 子句。

 CREATE FUNCTION [udf_Holidays](@Year char(4))
    RETURNS @Holidays TABLE (
        HolidayName NVARCHAR(30),
        HolidayDate DATETIME,
        [DayOfWeek] NVARCHAR(15)
    )
AS
BEGIN
DECLARE @Date datetime
DECLARE @Holiday datetime

---- New Years Day
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-01-01' )
IF DATENAME( dw, @Date ) = 'Saturday'
    SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
    SET @Date=@Date+1
INSERT INTO @Holidays
SELECT  'New Years Day', @Date, DATENAME( dw, @Date )-- 'DayOfWeek'

---- Martin L King's Birthday ( 3rd Monday in January )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-01-01' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 18-datepart( day, @Date ), @Date ) ), 0 ) -- 3rd Monday of the Month
INSERT INTO @Holidays
SELECT 'Martin L King’s Birthday', @Holiday , DATENAME( dw, @Holiday ) --'DayOfWeek'

---- President’s Day ( 3rd Monday in February )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-02-01' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 18-datepart( day, @Date ), @Date ) ), 0 )-- 3rd Monday of the Month
INSERT INTO @Holidays
SELECT 'President’s Day', @Holiday , DATENAME( dw, @Holiday ) --'DayOfWeek'

---- Memorial Day ( Last Monday in May )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-05-01' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 30-datepart( day, @Date ), @Date ) ), 0 ) -- 5th Monday of the Month
INSERT INTO @Holidays
SELECT 'Memorial Day', @Holiday , DATENAME( dw, @Holiday ) --'DayOfWeek'

---- Independence Day ( July 4 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-07-04' )
IF DATENAME( dw, @Date ) = 'Saturday'
    SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
    SET @Date=@Date+1
INSERT INTO @Holidays
SELECT 'Independence Day', @Date , DATENAME( dw, @Date ) --'DayOfWeek'

---- Labor Day ( 1st Monday in September )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-09-01' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 6-datepart( day, @Date ), @Date ) ), 0 )
INSERT INTO @Holidays-- 1st Monday of the Month
SELECT 'Labor Day', @Holiday, DATENAME( dw, @Holiday ) --'DayOfWeek'

---- Columbus Day ( 2nd Monday in October )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-10-01' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 12-datepart( day, @Date ), @Date ) ), 0 ) -- 2nd Monday of the Month
INSERT INTO @Holidays
SELECT  'Columbus Day', @Holiday, DATENAME( dw, @Holiday )-- 'DayOfWeek'

---- Veteran’s Day ( November 11 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-11-11' )
IF DATENAME( dw, @Date ) = 'Saturday'
    SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
    SET @Date=@Date+1
INSERT INTO @Holidays
SELECT 'Veteran’s Day', @Date , DATENAME( dw, @Date ) --'DayOfWeek'

---- Thanksgiving Day ( 4th Thursday in November )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-11-04' )
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 22-datepart( day, @Date ), @Date ) ), 0 )+3 -- 4th Thursday of the Month
INSERT INTO @Holidays
SELECT 'Thanksgiving Day', @Holiday , DATENAME( dw, @Holiday ) 'DayOfWeek'

---- Christmas Day ( December 25 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-12-25' )
IF DATENAME( dw, @Date ) = 'Saturday'
    SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
    SET @Date=@Date+1
INSERT INTO @Holidays
SELECT 'Christmas Day', @Date , DATENAME( dw, @Date ) --'DayOfWeek'

    RETURN;
END;

原文由 David Carroll 发布，翻译遵循 CC BY-SA 4.0 许可协议