Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Date difference between two dates by excluding saturday and sunday

Answered Date difference between two dates by excluding saturday and sunday

  • Thursday, February 23, 2012 6:11 AM
     
     
    Sign In to Vote
    0

    Hi Friends

    I have a table which contains three columns such as from date and todate.There is another columns as DateDifference which calculates the difference between the first two columns by leaving or excluding satur day and sunday.

    Please share your valuable ideas with me.

    With Thanks

    Sidhanta Tripathy


    • Edited by Sidhanta Thursday, February 23, 2012 6:14 AM
    • Moved by KJian_ Monday, February 27, 2012 12:57 AM (From:SQL Server Express)
    •  
     

All Replies

  • Thursday, February 23, 2012 10:45 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hopefully this helps

    DECLARE @d1 datetime
, @d2 datetime
SET datefirst  1
SET @d1 = '2012-02-04'
SET @d2 = '2012-02-26'
SET datefirst 7
SELECT DATEDIFF(dd, CASE WHEN datepart(dw,@d1) in (1,7) THEN DATEADD(dd,(datepart(dw,@d1)%5),@d1)
					 ELSE @d1 END
				, @d2)
				
				-(DATEDIFF(wk, CASE WHEN datepart(dw,@d1) in (1,7) THEN DATEADD(dd,datepart(dw,@d1)%5,@d1)
					 ELSE @d1 END, @d2))*2
				-CASE WHEN datepart(dw,@d2) =1 THEN 2
					WHEN datepart(dw,@d2) = 7 THEN 1
					ELSE 0 END

    The idea is

    Set Datefirst, so that Sunday is 1, Saturday is 7, the datepart(dw,@d1)%5 then either adds 1 or 2

    This is to start on a non weekend day, I suspect you want Sat/Sun to be Monday, and not Sunday, diff being if your end date was Monday you would have 0 days as opposed to 1

    Work out the Datediff in days

    Take the weekeds off for the number of weeks

    Then if you are ending on a saturday/sunday (taking into account your formulae now starts on the Monday), remove the count of days.

    Clear as mud? Not fully tested but I believe its on the right track.

     
  • Friday, February 24, 2012 1:57 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Take a look at this blog post

    Calculating number of workdays between 2 dates

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog