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
Similar Reads
How to Find Day Name From Date in SQL Server? Finding the day name from a specific date is a common task in SQL Server, useful for generating reports, analyzing trends, or scheduling. SQL Server provides two primary methods such as the DATENAME() function and the FORMAT() function. In this article, We will learn about How to Find Day Name From
4 min read
How to Extract Day of Week From Date Field in PostgreSQL? In PostgreSQL, extracting the day of the week from a date field is a common and essential task when working with temporal data. Knowing the day of the week is crucial for various applications, including scheduling, reporting, and analytical purposes. PostgreSQL provides multiple methods to achieve t
5 min read
How to get the Day of the Week in jQuery ? The Date() constructor will not give the day of the week directly. It returns a number for the weekdays starting from 0 to 6. You need to implement some extra methods in jQuery to get the day of the week as listed below: Table of Content Using Date.getDay() methodUsing moment.jsUsing Date.getDay() m
2 min read
SQL Query to Set First Day of the Week in a Database In this article, we are going to write a SQL script to Set the first day of the week in a Database. Below is the implementation for the same. You should have MSSQL in your server. Query in SQL - A query is a request for data or information from a database table or combination of tables. This data ca
2 min read
How to Create Daily, Weekly, and Monthly Report in SQL Server? SQL Server is a versatile database and a most used database throughout the world. In this article, let us see SQL queries how to get Daily, Weekly, and Monthly reports from SQL Server. Let us start in creating a database and sample details Step 1: Database creation Command to create the database. He
3 min read