How to Calculate Work Days Between Two Dates Using T-SQL?

Efficiently Calculating Workdays in T-SQL

Determining the number of working days between two dates is a common requirement in SQL Server. This is crucial for project scheduling, employee leave calculations, and other business applications. This guide demonstrates a robust T-SQL approach.

A T-SQL Solution for Workday Calculation

The following steps outline a method for calculating workdays in SQL Server using Transact-SQL (T-SQL):

1. Variable Declaration: Begin by declaring variables to hold the start and end dates.
2. Date Assignment: Assign your start and end dates to these variables.
3. Workday Count Calculation: The core calculation utilizes this formula:

(DATEDIFF(dd, @StartDate, @EndDate) 1) - (DATEDIFF(wk, @StartDate, @EndDate) * 2)

This formula calculates the total number of days, subtracts weekend days (two per week), and accounts for scenarios where the start or end date falls on a weekend.

Illustrative Example

Let's illustrate with a practical example:

<code class="language-sql">DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '2023-03-06';
SET @EndDate = '2023-03-20';

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);</code>

Handling Holidays

For more accurate results, incorporate holidays into the calculation:

1. Holiday Table Creation: Create a table to store holiday dates for the relevant year(s).
2. Query Enhancement: Modify the query to exclude holiday dates from the workday count by joining with the holiday table. This requires a more sophisticated query, potentially using a CTE (Common Table Expression) for improved readability and maintainability.

The above is the detailed content of How to Calculate Work Days Between Two Dates Using T-SQL?. For more information, please follow other related articles on the PHP Chinese website!