Solved

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

Posted on 2015-02-14
5
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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 48

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 48

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

733 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