JamesNT
asked on
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:
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
Itzik Ben-Gan
You might have noticed that I didn't use between in my solution.
Please see: "Beware of Between"
WHERE col >= '20120101' AND col < '20120201'http://sqlmag.com/t-sql/t-sql-best-practices-part-2
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
ASKER
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
Regardless, your taking the time to point all that out was very excellent of you. :)
JamesNT
No pronlem, it good you know the issue exi st ts, oh do many do not.
Cheers.
Paul
Cheers.
Paul
ASKER
JamesNT