In A Query Return Number Of Days Between Two Dates

Question

In my query, I need a way to return the number of days (excluding, holidays and weekends) between two dates.

Let's say I have this DDL
create Table TestData
(
inputdate datetime
,releasedate datetime
)

Insert Into TestData (inputdate, releasedate) VALUES
('2020-08-30 23:59:59.997','2020-09-04 12:59:59.997')

How can I do a query like this
Select
inputdate
,releasedate
,[Num Of Days To Process] = datediff(xxxxxx)

This is for SQL Server 2016

Answer 1

create Table TestData
(
inputdate datetime
,releasedate datetime
)

 Insert Into TestData (inputdate, releasedate) VALUES
 ('2020-08-30 23:59:59.997','2020-09-04 12:59:59.997')

 Select
inputdate
,releasedate
,[Num Of Days To Process] = datediff(Day,inputdate, releasedate)
from TestData

 drop Table TestData

Answer 2

Jingyang's response simply gives you the number of calendar days.

If you only want to exclude weekends this could be done with some complicated math (that I don't have handy)., but if you also want to exclude business holidays that falls on Monday to Friday, you need to have a calendar table. Ed Pollack has a good article about creating a calendar table: https://www.sqlshack.com/designing-a-calendar-table/.

Answer 3

Hi @Johnathan Simpson ,

We need to create a temporary table for holidays to store information about holidays.The following code can return the number of days between two dates(excluding, holidays and weekends).
Please check:

create Table TestData  
(inputdate datetime  
,releasedate datetime)  
Insert Into TestData (inputdate, releasedate) VALUES  
 ('2020-08-30 23:59:59.997','2020-09-04 12:59:59.997')  
CREATE TABLE tb_Holiday(  
HDate smalldatetime primary key clustered, --Holiday date  
Name nvarchar(50) not null)             --Holiday name  
insert into tb_Holiday values('10-1-2020','National Day'),('09-02-2020','ValentinesDay')  
  
  
CREATE FUNCTION dbo.f_WorkDay(  
@dt_begin datetime,  --Calculation start date  
@dt_end  datetime    --End date of calculation  
)RETURNS int  
AS  
BEGIN  
DECLARE @workday int,@i int,@bz bit,@dt datetime  
IF @dt_begin>@dt_end  
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt  
ELSE  
SET @bz=0  
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,  
@workday=@i/7*5,  
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)  
WHILE @dt_begin<=@dt_end  
BEGIN  
SELECT @workday=CASE   
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5  
THEN @workday+1 ELSE @workday END,  
@dt_begin=@dt_begin+1  
END  
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)  
END  
GO   
  
select inputdate,releasedate,  
[Num Of Days To Process] =dbo.f_WorkDay(inputdate,releasedate)-  
(select count(*) from tb_Holiday where HDate between inputdate and releasedate)  
from TestData  
  
  
drop table TestData  
drop table tb_Holiday  
drop function dbo.f_WorkDay  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

You should use a calendar table:

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

See "udf_AddBusinessDays" as an example to create your own count function.

Answer 5

Hi @Johnathan Simpson ,

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Echo