Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Exclude Weekends in SQL Query or Get Records Without Weekends

Nov 28, 2012
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.
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 RSS subscribe by email Subscribe by Email

8 comments :

SImplyAsp said...
This comment has been removed by the author.
Anonymous said...

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...

shrinivas said...

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. . .

Suresh Dasari said...

@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

Nikhil Kadu said...

it works.....thnku sir

Anonymous said...

select top 50 * from TABLENAME where (DATENAME(dw, createdat)) <>'Saturday' and (DATENAME(dw, createdat)) <>'Sunday' -:Kavish

Anonymous said...

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")

Deepak said...

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

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.