get DATEDIFF excluding weekends using sql server

by

Example query below, here are some details on how I solved it.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

This doesn’t behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you’ll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end

More Related Contents:

  1. What is User define function and how to execute that? [closed]
  2. Retrieving last record in each group from database – SQL Server 2005/2008
  3. Base64 encoding in SQL Server 2005 T-SQL
  4. Integer division in sql server
  5. Nested stored procedures containing TRY CATCH ROLLBACK pattern?
  6. SQL query to find Missing sequence numbers
  7. How to use alias column name in where clause in SQL Server
  8. How do I move files to an archive folder after the files have been processed?
  9. Convert .NET Ticks to SQL Server DateTime
  10. Executing dynamic SQL in a SQLServer 2005 function
  11. subquery or leftjoin with group by which one is faster?
  12. Advantages of SQL Server 2008 over SQL Server 2005? [closed]
  13. Calculating timespan with t-sql
  14. Isoweek in SQL Server 2005
  15. How to delete all rows from all tables in a SQL Server database?
  16. Convert SQL server datetime fields to compare date parts only, with indexed lookups
  17. How to update data in one table from corresponding data in another table in SQL Server 2005
  18. Foreign key relationship with composite primary keys in SQL Server 2005
  19. Unique Key Violation in SQL Server – Is it safe to assume Error 2627?
  20. How to recover deleted rows from SQL server table?
  21. What good are SQL Server schemas?
  22. What is difference between datetime and timestamp
  23. How do I check if a Sql server string is null or empty
  24. Problem with Renaming a Column in SQL Server
  25. SQL Server 2005 Computed Column Is Persisted
  26. SQL Server: How to permission schemas?
  27. Aggregate SQL Function to grab only the first from each group
  28. SQL Server: How to tell if a database is a system database?
  29. Multiple Select Statements using SQL Server 2005 “WITH” Statement
  30. How do you store a picture in an image column?

Leave a Comment