Solved

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

Posted on 2013-05-18
6
328 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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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)
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Post a good COM tutorial 1 34
ms sql stored procedure 22 75
how to fix this error 14 45
Updating ms sql with special characters 8 19
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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

10 Experts available now in Live!

Get 1:1 Help Now