Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-18
6
Medium Priority
?
336 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
[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
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 48

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
Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

 
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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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