Introduction:
In this SQL Server article I will explain how to write query to get records Exclude weekends data or without weekends data in SQL Server.
In this SQL Server article I will explain how to write query to get records Exclude weekends data or without weekends data in SQL Server.
Description:
In
previous articles I explained insert single quote data in database, DateAdd function Example, Pass table as parameter to procedure, difference between function and stored
procedures,
joins in SQL Server and many
articles relating to SQL
Server.
Now I will explain how to write a query to get records without weekend’s data
in SQL
Server.
During
work with one application I got requirement like get records from database by
excluding weekend’s data for that we need to write the query like as shown
below
SELECT * FROM yourtable WHERE ((DATEPART(dw, yourdatecolumn) + @@DATEFIRST) % 7) NOT IN (0, 1)
|
Here
@@DATEFIRST is used to Sets the first day
of the week to a number from 1 through 7.
In above
query we are taking date part of our datetime column and will add @@DATEFIRST value and divided
by 7 then we will get day particular day week based on 0 or 1 or 2 etc…
If
you want to see it in complete example first design one table (UserInfo) with two columns in database
and enter some dummy data like as show below
CrDate
|
DayOfDate
|
2012-11-28 09:00:00.000
|
Wednesday
|
2012-11-29 07:24:11.000
|
Thursday
|
2012-11-30 11:21:43.000
|
Friday
|
2012-12-01 06:32:11.000
|
Saturday
|
2012-12-02 09:55:22.000
|
Sunday
|
2012-12-03 10:30:31.000
|
Monday
|
2012-12-04 01:11:59.000
|
Tuesday
|
Now
from above table I need to get the records without weekend (Saturday and Sunday)
records for that we need to write the query like as shown below
SELECT * FROM UserInfo WHERE ((DATEPART(dw, Crdate) + @@DATEFIRST) % 7) NOT IN (0, 1)
|
Once
we run above query we will get output like as shown below
Output:
CrDate
|
DayOfDate
|
2012-11-28 09:00:00.000
|
Wednesday
|
2012-11-29 07:24:11.000
|
Thursday
|
2012-11-30 11:21:43.000
|
Friday
|
2012-12-03 10:30:31.000
|
Monday
|
2012-12-04 01:11:59.000
|
Tuesday
|
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
8 comments :
Thanks a lot Suresh...
I needed these functionality some 4 months back and since i wasn't able to do it in SQL , i'd to implement these logic from front end...
Wish you'd written this article earlier...
Excellent Query...
Thanks a lot...
I have got one requirement that in my table i'm having with the column names Fname, Lname and mobileNo. For example Fname=abc, Lname=xyz, mobileNo=9012345678. Then the result should be like
string PersonId=abcxyz5678;
From the above string object we can find out one thing that I am combining Fname,Lname,mobileNo together in a string object with the help of query but here I want to take only last four digits of the mobileNo through the query.
Could u plz solve my problem.
Thank you. . .
@Shrinivas...
if you want to get last four digits you need to use substring function for that check below post
http://www.aspdotnet-suresh.com/2012/04/sql-server-substring-function-example.html
it works.....thnku sir
select top 50 * from TABLENAME where (DATENAME(dw, createdat)) <>'Saturday' and (DATENAME(dw, createdat)) <>'Sunday' -:Kavish
Hi can you please help me to get this statement to exclude weekends, it doesn't recognize 'w' or 'dw' as weekdays, it still keeps calculating all days
totext(DateAdd ("d",20,{?maildt} ),"MMMM dd, yyyy")
How to get monthly attendance report in asp.net.I have a table tblAttendance(ClassId,SectionId,StudentId,rollNo,AttStatust,Date,Monthcode).I want data like this
StudentId RollNo 01-01-2019 02-01-2019 ..... totalPresent TotalApsent
1001 01 P A 1 1
1002 02 A A 0 2
Note: Only a member of this blog may post a comment.