How to Exclude Weekend Days in a SQL Server Query?

With this article, we will learn how to exclude weekend days in a SQL server query. For this task, we use the DATEADD ( ) MS.SQL server function. This function in SQL Server is used, to sum up, a time or a date interval to a specified date then returns the modified date.

Syntax :

DATEADD(interval, number, date)

So, we will create a database first:

Step 1: Create database:

Query:

CREATE DATABASE GFG

Step 2: Using the database 

USE GFG

Step 3: Create a table 

Create a table (EmployeeCalender) in the database to store the data.

CREATE TABLE EmployeeCalender (
 EmpCalDate datetime NOT NULL ,
 IsBusinessDay char (1)
)

Step 4: Insert data into a table

Query:

/* Data Inserted for a full week dates*/

INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/06/2021','Y')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/07/2021','Y')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/08/2021','Y')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/09/2021','Y')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/10/2021','Y')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/11/2021','N')
INSERT INTO EmployeeCalender (EmpCalDate, IsBusinessDay) VALUES ('9/12/2021','N')

Step 5: Now we will write the SQL query which will get all the weekdays except non-working days.

Query:

SELECT * FROM EmployeeCalender WHERE EmpCalDate >= DATEADD(d,-7,GETDATE())
AND
EmpCalDate < DATEADD(d,7,GETDATE())
AND
IsBusinessDay ='Y'

The output of dates excluding weekends