While working with one of the applications, I came across a requirement to get records from the database by excluding weekend data. So 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 takingthe 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.
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:
- CREATETABLE CheckInDetails
- (
- [ID] INTIDENTITY(1, 1) NOT NULL, [CheckInDate] DATENOT NULL, [Weekday] VARCHAR(MAX) NULL
- )
- INSERTINTO 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 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:
- SELECT *
- FROM CheckInDetails
- WHERE ((DATEPART(dw, CheckInDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
Once we run above query we will get 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 answer depends on your server's week -- start day set up, so it's either.
Excluding Saturday and Sunday: If Sunday is the first day of the week for your server,
- SELECT [date_created]
- FROM table
- 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 table
- WHEREDATEPART(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
- WHEREDATEPART(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
- WHEREDATEPART(w,[date_created]) NOT IN (7)
Hope you have enjoyed this post. Please feel free to share it with your friends and let us know your doubts in the comments below.
What do you think?
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.