Beyond Weekends: Addressing Holidays and Custom Workweeks in Your SQL Workday Count
Counting Work Days Between Two Dates in SQL
Problem:
Given a start date and an end date, we want to find the number of work days (typically Monday to Friday) between them.
Solutions:
There are two main approaches to this problem in SQL:
Using Date Difference and Day of Week Functions:
This method utilizes built-in functions to calculate the total difference in days and then excludes weekends:
SELECT DATEDIFF(day, @StartDate, @EndDate) -
(DATEDIFF(week, @StartDate, @EndDate) -
DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate))) -
CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END + 1 AS WorkDays;
Explanation:
DATEDIFF(day, @StartDate, @EndDate)
: Calculates the total difference in days between the start and end dates.DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate))
: This adjusts for the case where both start and end days are weekends.CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END
: This accounts for the possibility of the start date being a Saturday or Sunday.
Looping Through Dates (Advanced):
This method iterates through each date between the start and end date, checking if it's a weekday and adding the count:
DECLARE @WorkDays INT = 0,
@CurrentDate DATE = @StartDate;
WHILE @CurrentDate <= @EndDate
BEGIN
IF DATEPART(WEEKDAY, @CurrentDate) NOT IN (1, 7)
SET @WorkDays = @WorkDays + 1;
SET @CurrentDate = DATEADD(day, 1, @CurrentDate);
END;
SELECT @WorkDays AS WorkDays;
- This method uses a loop variable
@CurrentDate
starting at the@StartDate
. - It checks the day of the week using
DATEPART(WEEKDAY, @CurrentDate)
. - If it's a weekday (not Saturday or Sunday), it increments the
@WorkDays
counter. - The loop continues until
@CurrentDate
is greater than@EndDate
.
Related Issues and Solutions:
- Holidays: If you need to exclude specific holidays, you can join with a table containing holiday dates and filter them out in the calculation.
- Weekends Definition: Some workweeks might differ from Monday-Friday. You can modify the
CASE
statement or loop condition to adjust for your specific definition. - Date Format: Ensure your dates are formatted consistently and compatible with your SQL implementation.
sql t-sql date