?
Solved

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

Posted on 2015-02-14
5
Medium Priority
?
182 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 49

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 49

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 49

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

801 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