How to Calculate the Number of Workdays Between Two Dates in SQL Server?

Calculating Business Days Between Dates in SQL Server

Efficiently determining the number of business days (Monday-Friday) between two dates is crucial for many SQL Server applications. This can be accomplished using a concise T-SQL expression. The following formula assumes a standard Monday-Friday work week:

<code class="language-sql">SELECT
    (DATEDIFF(day, @StartDate, @EndDate) + 1)
    - (DATEDIFF(week, @StartDate, @EndDate) * 2)
    - CASE WHEN DATENAME(weekday, @StartDate) = 'Sunday' THEN 1 ELSE 0 END
    - CASE WHEN DATENAME(weekday, @EndDate) = 'Saturday' THEN 1 ELSE 0 END</code>

Here, @StartDate and @EndDate represent the start and end dates. The calculation first finds the total number of days, then subtracts weekend days. It also handles edge cases where the start date is a Sunday or the end date is a Saturday.

For instance, with @StartDate = '2008/10/01' and @EndDate = '2008/10/31', the expression yields:

<code>(DATEDIFF(day, '2008/10/01', '2008/10/31') + 1)
- (DATEDIFF(week, '2008/10/01', '2008/10/31') * 2)
- CASE WHEN DATENAME(weekday, '2008/10/01') = 'Sunday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(weekday, '2008/10/31') = 'Saturday' THEN 1 ELSE 0 END</code>

This results in 21, representing the 21 business days between the given dates.

Remember: This calculation doesn't account for public holidays. For a more comprehensive solution including holidays, a more sophisticated query involving a holiday table would be necessary.

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