Count work days between two dates

by

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate="2008/10/01"
SET @EndDate="2008/10/31"


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

If you want to include holidays, you have to work it out a bit…

More Related Contents:

  1. How to return only the Date from a SQL Server DateTime datatype
  2. Best approach to remove time part of datetime in SQL Server
  3. How to calculate the number of “Tuesdays” between two dates in TSQL?
  4. SQL “between” not inclusive
  5. Date serial in SQL?
  6. How to create a specific SQL Server stored procedure? [closed]
  7. SQL Server dynamic PIVOT query?
  8. SQL Server – Best way to get identity of inserted row?
  9. How can I list all foreign keys referencing a given table in SQL Server?
  10. Split function equivalent in T-SQL?
  11. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
  12. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  13. SQL Server – How to lock a table until a stored procedure finishes
  14. What is SYSNAME data type in SQL Server?
  15. SQL query to split column data into rows
  16. How to drop SQL default constraint without knowing its name?
  17. Difference between datetime and timestamp in sqlserver? [duplicate]
  18. SQL Server SELECT to JSON function
  19. How to count instances of character in SQL Column
  20. Dynamic update statement with variable column names
  21. How to select only the first rows for each unique value of a column?
  22. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  23. Check if current date is between two dates Oracle SQL
  24. Count Returning blank instead of 0
  25. Unordered results in SQL
  26. Using PIVOT in SQL Server 2008
  27. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  28. How do I compare 2 rows from the same table (SQL Server)?
  29. SQL – How do I get only the numbers after the decimal?
  30. How to retrieve records for last 30 minutes in MS SQL?

Leave a Comment