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

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

• 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