How to find all fridays and holidays between two dates

Question

The table:

hDate      Holiday

17/12/2011 National Day
01/01/2012 New Year
....

From the table, i want to find the total number of holidays between two dates:

A query like:

select count(hdate)
from table1
where hdate between '" & start_date & "' and '" & end_date & "'

User input:

start_date = '16/12/2011' 
end_date = '15/01/2012' 

and also I want to find the friday between 2 dates.

For finding fridays, how to create a query?

Expected output:

Holiday Friday

2       5

[2] - 2 days holiday from table1, [5] - 5 days friday

How to do this?

Answer 1

This counts the fridays between 2 dates:

declare @from datetime= '2012-01-26'  
declare @to datetime  = '2012-01-28'

select datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 

The holidays are easy to find, it seems like you have that part covered already.

I sort of answered this earlier. But didn't get any credit:

How to calculate the number of "Tuesdays" between two dates in TSQL?

Answer 2

See:

Why should I consider using an auxiliary calendar table?

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

• How many business days between x and y?
• What are all of the dates between the second Tuesday of March and the first Friday in April? 
• On what date should I expect this shipment to arrive? 
• What were the dates of all the Fridays in this quarter? 
• ...  

Answer 3

That select help you:

DECLARE @FROMDATE DATE = '2009-01-07'
DECLARE @TODATE DATE = '2012-01-26'

SELECT COUNT(*) holidays,(select COUNT(*) from table1 where DATEPART(DW, hdate) = 5
AND DT BETWEEN @FROMDATE AND @TODATE ) fridays FROM table1 
WHERE hdate BETWEEN @FROMDATE AND @TODATE

Answer 4

1. For the holiday, your SQL looks fine, you just seem to have trouble plugging the parameters into the SQL. If you specify which programming language you are using, we might be able to help here. If you use .NET, you should use Parameterized Queries instead of string substitution.

2. For the fridays, see this question:

   • Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

Answer 5

select count(Holiday) as holiday
from Table1
where date between start_date AND end_date

Answer 6

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='16/12/2011' 
SET @EndDate = '15/01/2012' 


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Friday' THEN 1 ELSE 0 END)

Answer 7

We solving the problem with an extra time table. This looks like this

ID   | Date        | Holiday   | Year    | CalendarWeek | DayName
1    | 17/12/2011  | 1         | 2011    | 50           | Monday 
2    | 18/12/2011  | 0         | 2011    | 50           | Thursday
3    | 19/12/2011  | 0         | 2011    | 50           | Wendsday

With this table you could resolve your question like this

select 
(select count(d.DayName) from date_table as d 
     where d.DayName = 'Friday' and date >= start_date and date <= end_date ),
(select sum(d.Holiday) from date_table as d 
     where date >= start_date and date <= end_date )

This should also be SQL Server 2000 compatible. And this for SQL Server 2005 and above:

with tmp(id) as
(
 select id from  from date_table where date >= start_date and date <= end_date
)
select
 (select count(d.DayName) from date_table inner join tmp on tmp.id = id 
     where DayName = 'Friday' ),
 (select sum(d.Holiday) from date_table inner join tmp on tmp.id = id )