How to Exclude Saturday and Sunday Using SQL Query? 🤔 While working with one of the applications, I came across a requirement to get records from the SQL Server database by excluding the weekend’s data.
Topics Covered
Introduction
I was wondering at first but then divided the problem and worked on it part by part to get to the final result.
Hence, I thought of sharing it with you guys.
For excluding weekend data we need to write the query as:
SELECT *
FROM table
WHERE ((DATEPART(dw, CheckedInDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
Here @@DATEFIRST is used to set the first day of the week to a number from 1 through 7.
In the above query, we are taking the date part of CheckedInDate and will add @@DATEFIRST value. Further, the value will be divided by 7 then we will get day particular day of the week based on 0 or 1 etc.
Create Table and Insert Data
CREATE TABLE CheckInDetails
(
[ID] INT IDENTITY(1, 1) NOT NULL
, [CheckInDate] DATE NOT NULL
, [Weekday] VARCHAR(MAX) NULL
)
INSERT INTO CheckInDetails(CheckInDate, [Weekday])
VALUES ('2016-02-22', 'Monday'), ('2016-02-23', 'Tuesday’),
('2016-02-24', 'Wednesday'), ('2016-02-25', 'Thursday'),
('2016-02-26', 'Friday'), ('2016-02-27', 'Saturday'),
('2016-02-28', 'Sunday')
Now from the above table, we need to get the records without the weekend (i.e except Saturday and Sunday).
ID CheckInDate Weekday
-----------------------------------
1 2016-02-22 Monday
2 2016-02-23 Tuesday
3 2016-02-24 Wednesday
4 2016-02-25 Thursday
5 2016-02-26 Friday
6 2016-02-27 Saturday
7 2016-02-28 Sunday
For that we need to write the SQL query as shown below:
SELECT *
FROM CheckInDetails
WHERE ((DATEPART(dw, CheckInDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
Once we run above query we will get an output as shown below:
ID CheckInDate Weekday
------------------------------------
1 2016-02-22 Monday
2 2016-02-23 Tuesday
3 2016-02-24 Wednesday
4 2016-02-25 Thursday
5 2016-02-26 Friday
The answers depend on your server’s week — start day set up, so it’s either.
Exclude Saturday and Sunday Using SQL Query – Sunday
If Sunday is the first day of the week for your server,
SELECT [date_created]
FROM table
WHERE DATEPART(w,[date_created]) NOT IN (7,1)
OR
Exclude Saturday and Sunday Using SQL Query – Monday
If Monday is the first day of the week for your server,
SELECT [date_created]
FROM table
WHERE DATEPART(w,[date_created]) NOT IN (6,7) OR
If you want to only exclude Sunday and it is the first day of the week for your server,
SELECT [date_created]
FROM table
WHERE DATEPART(w,[date_created]) NOT IN (1)
OR
If you want to only exclude Sunday and it is not the first day of the week for your server,
SELECT [date_created]
FROM table
WHERE DATEPART(w,[date_created]) NOT IN (7)
That’s all folks, for this blog post. Concluding with the assumption that now you know How to Exclude Saturday and Sunday Using SQL Query?
You may also check out our more posts on SQL Server too.
I hope you have enjoyed this post. Please feel free to comment your thoughts below and also share it across with your friends
Happy Learning!
