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

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

CodePudding user response：

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

CodePudding user response：

Here is the query which provide your expected output like date between 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')