'Get working days (Monday-Friday) and replace them with "between" in query

I have some query in which I am substituting 2 dates in a field "between".

SELECT ...
FROM ...
WHERE o.EventDate between @startDate and @endDate

How to make it so that only business days are processed in the request. That is, if I pass, for example, startDay = '04/14/2022' , endDay = '04/28/22', then only business days (Monday-Friday) would be processed in the request

sqlsql-server


Solution 1:[1]

Here is the query which provides your expected output like date between the given date and week days from monday to friday:

SELECT columns 
FROM Table 
WHERE date_column between @startDate AND @endDate 
AND DATENAME(DW,date_column) not in ('Saturday','Sunday')

Solution 2:[2]

Here is the query which provide your expected output like date between given date and week days from Monday to Friday But some public holiday are not count in business days so for that you have to create master table which tells the public holidays which your organization consider out of business days and filter those days from your query

Here my query (SQL Server) consider first day of week is Sunday so range between 2-6 is used

SELECT columns FROM Table
WHERE date_column BETWEEN @startDate AND @endDate 
AND DATEPART(WEEKDAY, date_column) BETWEEN 2 AND 6

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rom Eh
Solution 2 Dale K

Related Questions