How to exclude Saturday and Sunday using SQL query

Here are samples of Oracle SQL queries to exclude Saturday and Sunday using SQL Query.

Suppose you’re querying a table to urge the info for a specific date range, and you would like to exclude the weekends. Below is an example.

Exclude Weekends in Oracle SQL Using to_char(date, ‘DY’) Function

exclude Saturday and Sunday using SQL Query


The following SQL query will get the last 30 days’ sales data, but weekends (Saturdays and Sundays) are excluded:

SELECT *

FROM sales_orders_details

WHERE TO_CHAR(order_date, ‘DY’) NOT IN (‘SAT’, ‘SUN’)

and trunc(order_date) >= trunc(sysdate) – 30;

Here the data output will include all days except Saturdays and Sundays.

While working with one of the applications, I found a requirement to purge records from the database by excluding weekend data. Now I am sharing this article.

To exclude the weekend we’d like to write down the query as follows.

SELECT *

FROM CheckInInfo

WHERE ((DATEPART(DW, CheckedInDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

Here @@DATEFIRST is employed to line the primary day of the week to variety from 1 through 7.

In the above query, we are taking the date as a part of the Checked In Date and can add the @@DATEFIRST value. Further, the worth is going to be divided by 7 then we’ll get day particular day of the week supported 0 or 1, etc.

If you would like to ascertain the entire example first design a table (Check-In Info) with three columns within the database and enter some random data as given below:

Let us write a SQL query for this.

CREATE TABLE CheckInInfo
(
[ID] INTIDENTITY(1, 1) NOT NULL, [CheckInDate] DATENOT NULL, [Weekday] VARCHAR(MAX) NULL
)
INSERTINTO CheckInInfo(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’)

IDCheck-in dateWeekDay
12021-09-20Monday
22021-09-21Tuesday
32021-09-22Wednesday
42021-09-23Thursday
52021-09-24Friday
62021-09-25Saturday
72021-09-26Sunday

Now from the above table, we’d like to urge the records without the weekend (i.e except Saturday and Sunday).

For that we’d like to write down the SQL query as shown below:

SELECT *
FROM CheckInInformation
WHERE ((DATEPART(DW, CheckInDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

Once we run the above query we’ll get the output as shown below:

IDCheck-in dateWeekDay
12021-09-20Monday
22021-09-21Tuesday
32021-09-22Wednesday
42021-09-23Thursday
52021-09-24Friday

The answer depends on your server’s week — start day found out, so it’s either.

Excluding Saturday and Sunday: If Sunday is the first day of the week for your server

SELECT [date_created]
FROM CheckinInfo
WHEREDATEPART(w,[date_created]) NOT IN (7,1)


OR

Excluding Saturday and Sunday: If Monday is the first day of the week for your server,

SELECT [date_created]
FROM CheckInInfo
WHEREDATEPART(w,[date_created]) NOT IN (6,7)

OR

If you would like to exclude only Sunday and it’s the primary day of the week for your server,

SELECT [date_created]
FROM CheckInInfo
WHEREDATEPART(w,[date_created]) NOT IN (1)

OR

If you would like to exclude only Sunday and it’s not the primary day of the week for your server,

SELECT [date_created]
FROM CheckInInfo
WHEREDATEPART(w,[date_created]) NOT IN (7)

Hope you’ve got enjoyed this post. Please be happy to share it together with your friends and allow us to know your doubts within the comments below.

What is your opinion?

If you’ve got any questions or suggestions regarding how to exclude Saturday and Sunday using SQL Query please be happy to email us or put your thoughts as comments below. we might like to hear from you. If you found this post useful then please share it alongside your friends and help them to find out.

You May Also Like