How to exclude Saturday and Sunday using SQL Query?

How to exclude Saturday and Sunday using SQL query?

Howdy Reader,

While working with one of the application, I came across a requirement to get records from database using SQL query by excluding weekend’s data. So I thought of sharing it with you guys.

For excluding weekend’s data we need to write the query as:

Here @@DATEFIRST is used to set the first day of the week to a number from 1 through 7.

In above query we are taking 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.

How to exclude Saturday and Sunday using SQL Query

If you want to see the complete example first design a table (CheckInDetails) with three columns in database and enter some random data as given below:

Scripts:

Now from above table, we need to get the records without 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:

Once we run above query we will get output like 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 answer depends on your server's week-start day set up, so it's either. Click To Tweet

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

OR

Excluding Saturday & Sunday If Monday is the first day of the week for your server

OR

If you want to only exclude Sunday & it is the first day of the week for your server

OR

If you want to only exclude Sunday & it is not the first day of the week for your server

If you have a daily running job/query and you don’t want it to execute on Sunday, then you may write a query like this:

Hope you have enjoyed this post. Please feel free to share it with your friends and let us know your doubts in comments below.

What do you think?

Dear Reader,
If you have any questions or suggestions please feel free to email us or put your thoughts as comments below. We would love to hear from you. If you found this post or article useful then please share along with your friends and help them to learn.

Happy Learning :)