Menu
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 provides all the public holidays for the next say 50 years.
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.
Does anyone know if Sql server has any support for public holidays or an algorithm of some sort to work them out? Or does anyone have somewhere official that I can bulk insert from.
445
Using the code I make frequent use of the DATEPART, DATEADD, DATENAME functions available in SQL at the top of the function to ease typing, and follow that up with merely checking values to determine whether or not the specified date is a federal holiday.
You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
Use the DATENAME() function and specify the datepart as weekday . select ID, Name, Salary, Date from dbo. yourTable where datename(weekday, Date) in ('Saturday', 'Sunday');
I just gather information over the internet and I come with this easy way to calculate the US Bank Holidays.
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]
Isn't a public holiday very dependent of locale?
Programatically, there is no way to compute this. Alternatively, you will need to find an official list of holidays for each of your locales. And even so, you will not get the granularity you want.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
