Calculate holidays or days between two dates in SQL Server

In this article, we learn about SQL Server dates functions and using these functions calculate the day's difference between two dates. Most useful functions when we develop applications database like timeline schedulers, appointment applications, etc. 

Tested Example with source code:

DECLARE @StartDate datetime='2020-06-26 00:00:00.000';
DECLARE @EndDate datetime='2020-06-28 00:00:00.000';
DECLARE @DaysCount int=DATEDIFF(day,@StartDate,@EndDate)+1;
print @DaysCount

DECLARE @Holidays int=0;
DECLARE @Cnt int=0;
WHILE @Cnt<@DaysCount
BEGIN
 IF DATENAME(WEEKDAY, @StartDate) IN ('Sunday')
 BEGIN
  SET @Holidays=@Holidays+1
 END;
 SET @Cnt=@Cnt+1;
END;

print @Holidays; 

SQL Server function for calculating days between two dates:

CREATE FUNCTION [dbo].[GetDateRangeHolidays](@StartDate datetime,@EndDate datetime)  
RETURNS int  
AS  
BEGIN  
 DECLARE @DaysCount int=DATEDIFF(day,@StartDate,@EndDate)+1;
 DECLARE @Holidays int=0;
 DECLARE @Cnt int=0;
 WHILE @Cnt<@DaysCount
 BEGIN
  IF DATENAME(WEEKDAY, @StartDate+@Cnt) IN ('Sunday')
  BEGIN
   SET @Holidays=@Holidays+1
  END;
  SET @Cnt=@Cnt+1;
 END;
 RETURN ISNULL(@Holidays,0);  
END;

Call function for test calculating days between two dates:

DECLARE @StartDate datetime='2020-06-20 00:00:00.000';
DECLARE @EndDate datetime='2020-06-28 00:00:00.000';
SELECT dbo.GetDateRangeHolidays(@StartDate,@EndDate);

Timeline Scheduler Example: