Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-18
6
Medium Priority
?
338 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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

Expert Comment

by:Dale Fye
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

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 49

Expert Comment

by:Dale Fye
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 49

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
Screencast - Getting to Know the Pipeline

885 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