Exclude Holiday and weekend from date ranges

1

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

2

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?

3

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?

4

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

5

Hello James and Jeff,

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

6

Hi Jim,

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

7

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?

8

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.

9

ok, Thanks Jim and Scott.