• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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

SELECT Order#, date1, date2
FROM TableA
WHERE date2 is between date1 and date1 -30 days
0
rhservan
Asked:
rhservan
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
Dale FyeCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
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
 
Dale FyeCommented:
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
 
PortletPaulCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now