How to Exclude Weekend Days in a SQL Server Query?
Last Updated :
14 Sep, 2021
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
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...