[AccessD] SQL between

Between #10/29/2002# And #11/12/2002# will return different results
depending on whether the underlying field in the table has a time value or
not.

If it does not have a time value, then all date values are implicitly some
date at 12 midnight. So Between #10/29/2002# And #11/12/2002# will return
all the records that have a date of 10/29/2002 to 11/12/2002 inclusive. 

However, if the date field does store a time value as well, explicitly, then
the exact same criterion Between #10/29/2002# And #11/12/2002# has a
different effect.

If will return all records where the date field is 10/29/2002 (at any time
of the day, as we implicitly have asked to start at midnight) up to
11/11/2002, plus any records which just happen to have a date value of
11/12/2002 00:00:00 AM. 

In other words, just any amount of time after midnight on 11/12/2002 is
sufficient to exclude a record from the results. So Between is , and always
has been inclusive, but it always uses the time part of date fields, which
is what trips some people up occasionally. :-)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
Sent: Friday, October 26, 2007 3:38 PM
To: _DBA-Access
Subject: [AccessD] SQL between


I have recently found a couple of issues in an app converted from A97 to
A2k3. The items of questions worked in A97 but fail in A2k3.

Problem 2. )

(Well, I guess this is more of a heads up.)

SQL between is not inclusive. So now I have to do a between DATE1 -1 and
between DATE2 +1

 

BTW I've always though it odd that it was inclusive but it seems to be many
of the SQL products but not in others. Standards!?



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com