I'm running a query with a where clause that uses
a "BETWEEN '1/1/04' and '1/31/04'", and I'm not getting
any rows returned for Jan 31. I thought BETWEEN was
inclusive? Anyone know what's going on here?

If you will write as:

Date < '1/31/2004'

It is equivalent to writing Date < '1/31/2004 00:00:00'. Therefore, You
won't get the dates with values:

1/31/2004 4:30
1/31/2004 2:20
1/31/2004 5:20

Here's the example:

CREATE TABLE TestTable
(
EmpID INT,
DateOfBirth SMALLDATETIME
)

INSERT INTO TestTable VALUES (1, '1/1/2004')
INSERT INTO TestTable VALUES (2, '1/2/2004')
INSERT INTO TestTable VALUES (3, '1/3/2004')
INSERT INTO TestTable VALUES (4, '1/1/2004')
INSERT INTO TestTable VALUES (5, '1/31/2004')
INSERT INTO TestTable VALUES (6, '1/31/2004')
INSERT INTO TestTable VALUES (7, '1/31/2004 4:40')
INSERT INTO TestTable VALUES (8, '1/31/2004 3:50')
INSERT INTO TestTable VALUES (9, '2/1/2004')
INSERT INTO TestTable VALUES (10, '2/1/2004 2:50')

--First run this:
SELECT * FROM TestTable
WHERE DateOfBirth BETWEEN '1/1/2004' AND '1/31/2004'

--Now run this
SELECT * FROM TestTable
WHERE DateOFBirth >= '1/1/2004' AND DateOfBirth < '2/1/2004'

---
Rohtash Kapoor
http://www.sqlmantra.com



"tomg" <anonymous (AT) discussions (DOT) microsoft.com> wrote

thanks Rohtash, I assumed I would get data for all times
on the "31st" up to day 1 of the next month.