Solved

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

Posted on 2015-02-14
5
150 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

825 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