Date difference between two dates by excluding saturday and sunday

Date difference between two dates by excluding saturday and sunday

• Thursday, February 23, 2012 6:11 AM

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.

With Thanks

Sidhanta Tripathy

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

All Replies

• Thursday, February 23, 2012 10:45 PM

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

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