Solved

How do I write my where clause to be between dates?

Posted on 2013-05-18
6
329 Views
Last Modified: 2013-05-24
SELECT Order#, date1, date2
FROM TableA
WHERE date2 is between date1 and date1 -30 days
0
Comment
Question by:rhservan
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176910
first, please don't use 'between'
the best and most reliable method for selecting by date range is with a combination of
(  >=  and  <  )

I will assume the tableA.date2 field is actually datetime
and that you want all information from a given date (without time) - 30 days
I'll also assume you want everything for today in the example
DECLARE @StartDate as DATETIME, @EndDate as DATETIME

SET @EndDate = dateadd(day, datediff(day,0, getDate()) , 0) /* remove time */

SET @EndDate = dateadd(day, 1, @EndDate) /* adjust as needed, this is Tomorrow at 00:00:00 */

SET @StartDate = dateadd(day, -30, @EndDate)

SELECT Order#, date1, date2
FROM TableA
WHERE ( date2 >= @StartDate AND date2 < @EndDate )
;

Open in new window

Why not 'between'? please see: "Beware of Between"

ps:and by the way, if you were to use between, those dates in the question would be the in the wrong order (the low date must be first!) :)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39176960
perhaps a simper example, here I use cast(getdate() as date) which removes time from getate() , and in this example the selection would be for everything 30 days before today up to (but excluding) today
SELECT Order#, date1, date2
FROM TableA
WHERE ( date2 >= dateadd(day, -30 ,cast(getdate() as date)) AND date2 < dateadd(day, 0 ,cast(getdate() as date))  )
/* this is 30 days before today up to (but excluding) today */

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39177327
If you are doing this in Access, the syntax would be:

SELECT Order#, date1, date2
FROM TableA
WHERE date2 is between date1 and DateAdd("d", -30, date1)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39181762
There is no "is" in the syntax for BETWEEN, but please do heed the advice of the first Expert and avoid between dates when possible.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39181862
Don't know how I missed that 'is'.

Correct syntax would be:

SELECT Order#, date1, date2
FROM TableA
WHERE date2 BETWEEN DateAdd("d", -30, date1) AND date1

There is nothing wrong with using the syntax BETWEEN, as long as you understand what it will get you.  The BETWEEN operator is inclusive of the values on both sides of the AND, so

BETWEEN #4/20/13# and #5/20/13#

would seem to imply you want the records for #4/20/13# through #5/20/13#.  This will work if your date fields only store the date, but if they are written using the Now() function or include a time component then the example above would actually give you all the values between #4/20/13 00:00:00# AND #5/20/13 00:00:00#, which would exclude all of the other times on #5/20/13#.  In this case, if you wanted to get all the records dated on or after #4/20/13# to include all the records for #5/20/13# then you could use:

BETWEEN #4/20/13# AND #5/21/13#

The problem with this syntax is that if you have any records where the date field has a value of #5/21/13 00:00:00# then those records would also be included in the result set.

Personally, I tend to use the >= syntax as well, so in Access, that would look like:

SELECT Order#, date1, date2
FROM TableA
WHERE date2 >= DateAdd("d", -30, date1)
AND date2 < DateAdd("d", 1, Date1)

This would give you all of the records for the current date (regardless of the time associated with it) back through the day that precedes today by 30 days.  However, that actually returns 32 days worth of records.  If you actually want 30 days worth of records, that include today, you would need to change that to:


SELECT Order#, date1, date2
FROM TableA
WHERE date2 >= DateAdd("d", -28, date1)
AND date2 < DateAdd("d", 1, Date1)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39182994
There is "nothing wrong" with the syntax of between = sort of

the syntax is fine, no argument there, but

It's the common misconceptions about the syntax that cause problems. In my experience the earlier someone learns to avoid between when filtering for date ranges the better off they will be. Yes, between may be OK when the data precision is whole days without time, but this isn't always the case so you need to be alert to the data precision every time you use it. Whereas the alternative syntax using >= with < applies no matter what the data precision is.

This is one of the nightmarish scenarios caused by using between:

select whatever
from sometable
where adate between '2012-01' and '2012-12-31 23:59:59'

well not only is it a bit silly, it's also wrong because depending on what dbms type and version there is potential for records to exist milliseconds after 23:59:59

isn't it ever so much easier to just do this?

select whatever
from sometable
where adate >= '2012-01' and adate < '2013-01-01'

not only simpler, but this will also reliably detect all the required records.

"beware of between"
{+edit, correct really stupid error - sorry}
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now