none
Date difference between two dates by excluding saturday and sunday

    Question

  • 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)
    Thursday, February 23, 2012 6:11 AM

Answers

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

    Thursday, February 23, 2012 10:45 PM
  • 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

    Friday, February 24, 2012 1:57 AM

All replies

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

    Thursday, February 23, 2012 10:45 PM
  • 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

    Friday, February 24, 2012 1:57 AM