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.......



4 comments :

  1. Hi,

    Thank you for the logic. It works perfect excluding the toDate, but if we have to include same, I am not able to acheive it by including @count+1 as you mentioned at the end of your video. Could you please clarify

    ReplyDelete
    Replies
    1. For example,

      set @fromDate = '07/02/14'
      set @toDate = '07/22/14'

      Output from above query is 14, but my expected result is 15 since '07/22/14' is a weekday. No need to count toDate if it falls in weekend (either saturday or sunday)

      Thank in advance

      Delete
    2. I meant to say that you will have to manually increment the result by adding 1 day.
      suppose your final result is @result = 14

      select @result+1 = 15
      this will work in all the scenarios. Hope this clarifies your original question

      Delete
    3. Thank you for your quick response.
      Is there anyway we can include this part within the logic, since I have to calculate for huge number of rows.

      Delete