Exclude Weekends from SQL Datetime Query

A localisation proof solution for excluding weekends (Saturday, Sunday) from a date range in an SQL query.

Example

You want to get the total number of hours worked in a month, but the working week excludes the weekend days. Using this script you can pick the start and end of the month as your datetime parameters, and in the where clause this query will filter out any days that fall on a weekend.

Instructions

Exlude weekends from any datetime query. This solution is tolerant to any localisation settings on target server.

1. Work out the difference in days between the two dates. +1 to be inclusive of the first date.
2. Work out the difference in weeks between the two dates. *2 for the two weekend days.
3. Check for an edge case where if the first date is a sunday it is counted.
4. Check for an edge case where if the last date is a saturday it is missed.

Code

DECLARE @StartQuery DATETIME = '10/01/2022';
DECLARE @EndQuery DATETIME = '10/31/2022';

--Work out total days between start and end, exluding weekends.
SELECT (datediff(dd, @StartQuery, @EndQuery)+1) - (datediff(wk, @StartQuery, dateadd(dd,1,@EndQuery)) * 2) 
    - CASE WHEN datename(WEEKDAY, @StartQuery) = 'Sunday' THEN 1 ELSE 0 END -- This includes for start date edge case
    + CASE WHEN DATEname(WEEKDAY, @EndQuery) = 'Saturday'  THEN 1 ELSE 0 END -- This includes for  end date edge case.

References

Bitbucket snippet.
Stack overflow thread.