Brett Kaiser (x002548) Blog

Wow...it's been 2 months...

It seems there's a common question discussiong how to eliminate holidays (and weekends) from date ranges.  And since I'm sick of doing a search to find it, I'd thought I'd post it.  Also, people ask about daylight savings time.  This is the same type of thing, but since it's regional, there the added twist of having to know where you are.  Anyway, here's the code:

CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GO

SET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = '1/3/2004', @x = 1

--Add WeekEnds
WHILE @x < 52
BEGIN
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT DATEADD(ww,@x,@FirstSat),   'SAT' UNION ALL
SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN'
SELECT @x = @x + 1
END
SET NOCOUNT OFF
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- Add US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT '1/1/2004',   'THU' UNION ALL
SELECT '2/16/2004',  'MON' UNION ALL
SELECT '5/31/2004',  'MON' UNION ALL
SELECT '7/5/2004',   'MON' UNION ALL
SELECT '9/6/2004',   'MON' UNION ALL
SELECT '11/25/2004', 'THU' UNION ALL
SELECT '11/26/2004', 'FRI' UNION ALL
SELECT '12/24/2004', 'FRI' UNION ALL
SELECT '12/31/2004', 'FRI'
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO


-- WeekDays in May

DECLARE @Start datetime, @End datetime
SELECT @Start = '5/1/2004', @End = '6/1/2004'

SELECT  DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
  FROM WeekEndsAndHolidays
WHERE DayOfWeekDate BETWEEN @Start AND @End
GO