How to Calculate Workdays Between Two Dates in SQL Server?

Calculate working days between two dates in SQL Server

Calculating the number of working days between two given dates is a valuable tool for scheduling, time management, and a variety of other business applications. In SQL Server, you can directly calculate the working days from Monday to Friday using T-SQL.

Solution:

To determine the number of working days between two dates (@StartDate and @EndDate), you can use the following formula:

(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)

Formula breakdown:

• DATEDIFF(dd, @StartDate, @EndDate) Calculate the total number of days between two dates, including weekends.
• DATEDIFF(wk, @StartDate, @EndDate) Calculate the number of complete weeks between two dates.
• (DATEDIFF(wk, @StartDate, @EndDate) * 2) represents the number of weekend days in the specified date range.
• (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) Check if the start date is Sunday, if so, subtract 1 day.
• (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) Check if the end date is a Saturday, if so, subtract 1 day.

Example:

To calculate the number of working days between October 1, 2008 and October 31, 2008, you can use the following query:

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)

This query will return result 22, which represents the number of working days between October 1 and 31, 2008, excluding weekends.

The above is the detailed content of How to Calculate Workdays Between Two Dates in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!