Thursday, October 3, 2013

Calculate Difference between two dates excluding weekends in SQL Server

In this article, we are going to calculate difference between two dates excluding weekends using SQL queries in SQL Server. 





-- Declaring variables to store from and to datetime values
declare @fromDate datetime, @toDate datetime
declare @dtToTempDate datetime

--Declaring a variable to store difference count
declare @count int

--Assigning values to @fromDate and @toDate
set @fromDate =  '10/6/2013'
set @toDate = '10/13/2013'

--if @fromDate is sunday then adding 1 day to it to increment 
--the date value to move to Monday
--if @fromDate is saturday then adding 2 day to it to increment 
--the date value to move to Monday
if datepart(dw, @fromDate)=1
       set @fromDate=@fromDate+1
else if datepart(dw, @fromDate)=7
       set @fromDate=@fromDate+2

--storing the @toDate value in a temp variable
set @dtToTempDate = @toDate

--if @fromDate is sunday then substracting 2 days to it to 
--decrement the date value to move to Friday
--if @fromDate is saturday then substracting 1 day to it to 
--decrement the date value to move to Friday
if datepart(dw, @toDate)=1
        set @toDate=@toDate-2
else if datepart(dw, @toDate)=7
        set @toDate=@toDate-1

--Difference between newly calculated @fromDate and @toDate    
select @count = datediff(dd, @fromDate, @toDate) - (datediff(wk, @fromDate, @toDate) * 2)

--Adding 1 to @count if @toDate was falling in Saturday or 
--Sunday    
select @count=case when datepart(dw, @dtToTempDate) in (1,7) then @count+1 else @count end

--Displaying the result 

select @count

Output:-








Remember datediff function excludes end date in calculation. So if want to include end date count, you need to add 1 day in the final count.

This is really easy to implement and it works perfectly fine without any issues.

Hope this helps.......