我有一个用C#编写的应用程序，不能在公共(public)假期或周末运行。我四处张望，没有发现(官方的)任何地方可以提供接下来50年的所有公共(public)假期。
如果我能得到这些，我将简单地将它们批量插入到我的sql server Holidays表中并使用它。但是，我在任何地方都找不到此数据。
有谁知道Sql Server是否对公共(public)假期提供任何支持或某种算法来解决这些问题？还是有人在我可以批量插入的官方位置。
最佳答案
我只是通过Internet收集信息，并且通过这种简单方法来计算美国银行假期。
美国银行假期
==========================
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]
关于c# - 如何确定SQL Server中的公共(public)假期？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/457176/