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

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.

Who is Participating?
PortletPaulConnect With a Mentor Commented:
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

      *, 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
JamesNTAuthor Commented:
Thank you very much.

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.
Itzik Ben-Gan
JamesNTAuthor Commented:
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.  :)

No pronlem, it good you know the issue exi st ts, oh do many do not.

All Courses

From novice to tech pro — start learning today.