如何确定SQL Server中的公众假期？ [英] How do I determine a public holiday in Sql server?

问题描述

我已经写在C＃应用程序不能在公共节假日或周末运行。我四处张望了一下，并没有发现任何地方（官方），提供所有的下一个公众假期说50年。

I have an application written in c# that cannot run on a public holiday or a weekend. I've looked around a bit and haven't found anywhere (official) that provide all the public holidays for the next say 50 years.

如果我可以得到这些，我就简单BULK INSERT他们到我的SQL Server假日表并使用它。但是，我找不到任何地方该数据。

If I can get these, I will simply bulk insert them into my sql server Holidays table and use that. However, I can't find this data anywhere.

有谁知道如果SQL Server有公众假期，或任何支持某种算法来解决它们呢？或任何人有地方官员，我可以从BULK INSERT

Does anyone know if Sql server have any support for public holidays or an algorithm of some sort to work them out? Or anyone got somewhere official that I can bulk insert from.

感谢

编辑：。为了讨论让只是假设我们想在英国公众假期!!!!

For arguments sake lets just assume we want public holidays in England!!!!

推荐答案

我只是收集信息在互联网上，我带着这个简单的。的方法来计算，美国银行假期

I just gather information over the internet and I come with this easy way to calculate the US Bank Holidays.

我使用这些信息在这个网站：

I use the information in this site:

的http://www.buyusa.gov/uk/en/us_bank_holidays.html

---

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]

这篇关于如何确定SQL Server中的公众假期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！