c# - 如何确定 Sql server 中的公共(public)假期？

Question

我有一个用 C# 编写的应用程序，无法在公共(public)假期或周末运行。我环顾四周，没有找到任何地方(官方)提供 future 50 年的所有公共(public)假期。

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

有谁知道 Sql server 是否支持公共(public)假期或某种算法来解决这些问题？或者有没有人有官方的地方我可以从中批量插入。

Answer 1

我只是通过互联网收集信息，然后用这种简单的方法来计算美国银行假期。

---

美国银行假期

===========================

DECLARE @Year char(4)
, @Date datetime
, @Holiday datetime

SET @Year = 2010

---- 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
SELECT @Date [New Years Day], 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
SELECT @Holiday [Martin L King's Birthday], 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
SELECT @Holiday [President’s Day], 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
SELECT @Holiday [Memorial Day], 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
SELECT @Date [Independence Day], 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 ) -- 1st Monday of the Month
SELECT @Holiday [Labor Day], 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
SELECT @Holiday [Columbus Day], 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
SELECT @Date [Veteran’s Day], 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
SELECT @Holiday [Thanksgiving Day], 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
SELECT @Date [Christmas Day], DATENAME( dw, @Date ) [DayOfWeek]

---- New Years Eve Day
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-12-31' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
    SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
    SET @Date=@Date+1
SELECT @Date [New Years Day], DATENAME( dw, @Date ) [DayOfWeek]