I've just added this for reference purposes.

-- declare temprary table to store dates

DECLARE @tblDateTime TABLE ([DateTime] DATETIME);

-- populate temporary dates to be used for reference purposes

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/01 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/02 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/03 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/04 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/05 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/06 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/07 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/08 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/09 15:00:00');

INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/10 15:00:00');

-- the following will not return the last date (2008/01/10')

SELECT *

FROM @tblDateTime

WHERE

[DateTime] BETWEEN '2008/01/01' AND '2008/01/10'

-- the following will return the last date (2008/01/10'). second being the recommended version as it has a performance benifit over convert.

SELECT *

FROM @tblDateTime

WHERE

CONVERT(VARCHAR(max), [DateTime], 111) BETWEEN '2008/01/01' AND '2008/01/10'

SELECT *

FROM @tblDateTime

WHERE

DATEADD(dd, DATEDIFF(dd, 0, [DateTime]),0) BETWEEN ('2008/01/01') and ('2008/01/10')

The first does not return a value as '2008/01/10' is read as '2008/01/10 00:00:00' and not '2008/01/10 23:59:59'.