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'