SQLTeam.com | Weblogs | Forums

Exclude Holiday and weekend from date ranges

Hello SQL Expert,

Is there a way I can query so my date ranges will exclude the Holiday and weekend
so I can get the total days within certain date ranges?

Here is my basic query:

Select StartDate, EndDate
From DateTbl

The easiest and most reliable way to solve this type of problem is using a calendar table that has the holidays and weekends identified. If your requirement was to exclude only weekends, you could do without a calendar table; just a numbers table would suffice.

Do you have a calendar table in your database?

Based on the name of "DateTbl", it would seem to be a "Calendar" table. Don't you have an "IsWorkDay" of some sort in the table that you could also filter on? Do you have an "IsHoliday" or similar column? A "DOW" (Day of Week) column?

The making of the work day itself is not hard, but holidays cannot be gotten by day of week:

DECLARE @PrevDay DATETIME DECLARE @FstDayOfWeek INTEGER SET @FstDayOfWeek = 1 -- Monday SET @PrevDay = DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @FstDayOfWeek) % 7 WHEN 1 THEN -2 WHEN 2 THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, GETDATE())); PRINT @PrevDay

Hello James and Jeff,

No, the DateTbl does not have the IsHoliday option but just date(s) and some other non dates fields.

Hi Jim,

Is this query does cover the weekend only correct? If so, how about Holiday?

Holidays are a problem without a calendar table that indicates which days are holidays and which are not. Holidays vary from region to region (July 4th is a holiday in the US, but nowhere else in the world). So how do you define what is a holiday and what is not, without a calendar table?

It doesn’t cover holidays, so you are only partway there. As suggested before I sent this, every country or locality has their own set of holidays and the easiest way to incorporate them for T-SQL to pick up is to put them in a table and compare by date.

In processes that have a front end, I prefer to have a .txt .ini file to pull them from in code. The .ini file remains obvious for someone to check, but a table can only be checked by a coder with permissions.

ok, Thanks Jim and Scott.