Substracting two dates while excluding week-ends

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Substracting two dates while excluding week-ends

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-15 : 10:02:42

Gregory Adrien writes "Is there a function in SQL that returns the number of days between two dates, not counting days that fall on week-ends."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-15 : 11:38:16

Not exactly, but you could cheat it a little by using this formula:

SELECT DateDiff(dd, date1, date2) - (2 * DateDiff(ww, date1, date2))

It'll count the total days (including weekend days), then subtract the number of weeks (multiplied by 2 for each weekend day)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-15 : 12:21:06

Needs a little adjusting for either end:


  DATEDIFF(day, date1, date2)
    - DATEDIFF(ww, date1, date2)*2
    - CASE WHEN DATEPART(dw, date2) = 7 THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(dw, date1) = 1 THEN 1 ELSE 0 END
    + 1

That's inclusive, for weekends of Saturday and Sunday where @@DATEFIRST = 7.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources