SQL Server - Find Business Days Between Dates

Recently, my client asked me to calculate the date difference, excluding only Sunday. How do you calculate days using the T-SQL script? Let's get the script in this article.

Script to calculate date difference excluding only Sunday

The following script finds the business days including Saturday. You can specify the dates for the parameter @d1 and @d2.

For example, I calculated business days (including Saturday) between two dates

declare @d1 datetime, @d2 datetime
select @d1 = '2021-11-01' ,  @d2 = '2021-12-01'
select datediff(dd, @d1, @d2) +case when datepart(dw, @d1) = 7 
then 1 else 0 end - (datediff(wk, @d1, @d2) * 1) -
 case when datepart(dw, @d1) = 1 then 1 else 0 end +
 case when datepart(dw, @d2) = 1 then 1 else 0 END

Date1: 2021-11-01

Date2: 2021-12-01

The script returns 26 days.

Let's verify in calendar, Yes, we have 26 days for the dates specified.

Note: The script does not include the last date (@d2) for calculation.

Script to calculate business days (excluding Saturday and Sunday)

Similarly, we might get a requirement to calculate only business days between the dates. In the calculation, we do not need to count Saturday and Sunday occurring between the dates.

Execute the following script:

declare @d1 datetime, @d2 datetime
select @d1 = '2021-11-01' ,  @d2 = '2021-12-01'

select datediff(dd, @d1, @d2) +case when datepart(dw, @d1) = 7
then 1 else 0 end - (datediff(wk, @d1, @d2) * 2) -
 case when datepart(dw, @d1) = 1 then 1 else 0 end +
 case when datepart(dw, @d2) = 1 then 1 else 0 END

The script returns 22 business days for the date range specified.

You can verify results using the following calendar.