Improve company productivity with a Business Account.Sign Up

x
?
Solved

Pull all dates that fall on a particular day of the week

Posted on 2015-02-14
5
Medium Priority
?
221 Views
Last Modified: 2015-02-15
SQL Server 2008 with all the latest service packs and patches as of November, 2014.

See the following SQL Query:

SELECT ID AS tID, BillID, TransactionID, Amount AS ChargeAmount
FROM Transactions
WHERE      (ClientID = @ClientID) 
AND (Date BETWEEN @StartDate AND @EndDate) 
AND DATEPART(weekday, Date)  = @DayOfWeek

Open in new window


This query is used in a SSRS report where the user can choose Monday, Tuesday, Wed., and so forth to see data on only those days in a date range.  Unfortunately, it's not working.  The user doesn't work on Sunday at all yet there is all kinds of stuff on Sunday.  Do I have the DATEPART in the WHERE CLAUSE correct?  In the SSRS report, I have a parameter set up with:

Monday = 1
Tuesday = 2
Wednesday = 3
Thursday = 4
Friday = 5
Saturday = 6
Sunday = 7

So @DayOfWeek gets passed whatever number of the day the user chooses from the parameter.

JamesNT
0
Comment
Question by:JamesNT
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40610307
try using modulo of 7 as the "day of week" reference instead of datepart because datepart is affected by dbms settings, however the modulo of 7 isn't

DECLARE @StartDate AS datetime
DECLARE @EndDate AS datetime
DECLARE @DayOfWeek AS int

SET @StartDate = '20150126'
SET @EndDate = DATEADD(DAY, 7, @StartDate)
set @DayOfWeek = 1

SELECT
      *, DATEPART(WEEKDAY, Date), datediff(day,-1,date) % 7, datename(weekday,date)
FROM Transactions
WHERE 1 = 1 -- (ClientID = @ClientID) -- I didn't bothere with this one

      AND (Date >= @StartDate AND date < @EndDate)
      AND datediff(day,-1,date) % 7 = @DayOfWeek
0
 

Author Closing Comment

by:JamesNT
ID: 40610983
Thank you very much.

JamesNT
0
 
LVL 50

Expert Comment

by:PortletPaul
ID: 40611299
Thanks. I should have mentioned that "between" is NOT a good option for date range filtering.


You might have noticed that I didn't use between in my solution.
Please see: "Beware of Between"

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
http://sqlmag.com/t-sql/t-sql-best-practices-part-2
Itzik Ben-Gan
0
 

Author Comment

by:JamesNT
ID: 40611396
Thank you again, Paul.  Fortunately, I am already very familiar with the overlapping problem of BETWEEN.  That's why I do not use it when I have to check out multiple date ranges.  This query uses only one date range in the WHERE clause and I do indeed want the upper and lower bounds included.

Regardless, your taking the time to point all that out was very excellent of you.  :)

JamesNT
0
 
LVL 50

Expert Comment

by:PortletPaul
ID: 40611465
No pronlem, it good you know the issue exi st ts, oh do many do not.

Cheers.
Paul
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question