top of page

SQL Server - Find Business Days Between Dates

Updated: Dec 31, 2021

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.

Script to calculate date difference excluding only Sunday

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.


Script to calculate business days (excluding Saturday and Sunday)

You can verify results using the following calendar.



193 views0 comments

コメント


bottom of page