Posted by Roy Harvey on 07/12/07 23:20

The DATETIME data type includes both date and time, and all
comparisons include both also. When we have the test:

WHERE ShipDate >= '20070405'
AND ShipDate <= '20070418'

What is really being said is:

WHERE ShipDate >= '2007-04-05 00:00:00.000'
AND ShipDate <= '2007-04-18 00:00:00.000'

Of course a ShipDate of '2007-04-18 11:35:03.000' is greater than one
of '2007-04-18 00:00:00.000', so the last day is excluded EXCEPT when
the time portion is all zeroes.

The standard practice testing for a range of dates is to test for less
than the next day.

WHERE ShipDate >= '20070405'
AND ShipDate < '20070419'

Roy Harvey
Beacon Falls, CT

On Thu, 12 Jul 2007 15:39:53 -0700, Aaron <odysseus183@hotmail.com>
wrote:

>I was trying to retrieve all the records from a table, Crates that
>fall within a particular date range for the field ShipDate, which is
>of type datetime. What I have noticed is that the lower bound is
>inclusive while the upper bound is not. For example, in this query, I
>have noticed that ship dates from 4/5/07 to 4/17/07 are included while
>ship dates from 4/18/07 are not:
>
>SELECT *
>FROM Crates
>WHERE (ShipDate IS NOT NULL) AND (ShipDate > '4/5/07') AND
>(ShipDate < '4/18/07')
>ORDER BY ShipDate
>
>I have also found that substiting > for >= and < for <= returns the
>exact same results. One solution would be to increase the upper bound
>by one day. However, I still find this strange. Is there a optimal
>way for dealing with date ranges with SQL Server 2005.
>
>Thanks,
>Aaron

[Back to original message]