How to Calculate Workdays Between Dates in SQL Server?

Calculate the number of working days between two dates using SQL Server

Determining the number of working days between two given dates is a common task in data analysis and report generation. In SQL Server, this calculation can be performed efficiently using T-SQL.

Query:

To calculate the number of working days (Monday to Friday) between two dates, execute the following query:

<code class="language-sql">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);</code>

Instructions:

• DATEDIFF(dd, @StartDate, @EndDate) 1 Calculate the total number of days between two dates including start date and end date.
• DATEDIFF(wk, @StartDate, @EndDate) * 2 Subtract the number of weekend days (2 days per week) from the total number of days.
• -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) Adjust the start date to Sunday.
• -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) Adjust the end date to Saturday.

Other considerations:

To include holidays in calculations, create a separate table to define the holiday dates and add them to the query. This requires additional logic to subtract the number of holidays contained within the specified period.

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