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

The datetime data type includes both date and time portions. When you set a
datetime value to '4/5/07' (btw, it will be better to use format like
'20070405' to avoid ambiguity in date format), you are effectively setting
the time portion to midnight. So, your condition in plain English is like
"select all rows where ShipDate is after midnight on 4/5/07 and before
midnight on 4/18/07". I assume your ShipDate values have time portion
according to when the shipment occurred. Because of that all rows for 4/5/07
are returned (except if you had a shipment exactly at midnight).

If you want to get all rows between 4/5/07 and 4/18/07 (inclusive), you can
write it like this:

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

See more about the datetime data type in the following article by Tibor
Karaszi:
http://www.karaszi.com/SQLServer/info_datetime.asp


HTH,

Plamen Ratchev
http://www.SQLStudio.com

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 (AT) hotmail (DOT) com>
wrote:

I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:

How to get the last day of the previous month? And the last day of the
current month?

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')

Thanks,
Aaron

Aaron (odysseus183 (AT) hotmail (DOT) com) writes:
SELECT dateadd(DAY, -1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01')

SELECT dateadd(DAY, -1,
dateadd(MONTH, 1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01'))


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Below alternative to the technique Erland posted. Although it's not as
clear as building a date string, it performs better in situations where
dates are calculated for each row. The overhead of converting to/from
date/string can add up for large tables.

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1,
0))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aaron" <odysseus183 (AT) hotmail (DOT) com> wrote

On Fri, 13 Jul 2007 14:13:30 -0700, Aaron wrote:

Hi Aaron,

When I copy and paste these queries into SSMS, I get the desired
results: June 30 and July 31. Your request for help to get them to work
implies that you got different results. Could you expand on that?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis