Between '6/1/08' and '6/30/08'

 date >= '6/1/08' and date < '7/1/08'

-- 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') 

--===== Create and populate the test table
CREATE TABLE #DateTest (TestDate DATETIME NOT NULL)

INSERT INTO #DateTest
SELECT TOP 39652 CAST(n.number+0.5 AS DATETIME) AS TestDate
FROM dbo.Numbers n

--===== Add a Primary Key to maximize performance
  ALTER TABLE #DateTest
        ADD CONSTRAINT PK_TestDate PRIMARY KEY CLUSTERED (TestDate)


DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2001/01/01'
SET @Enddate = '2002/01/10'
--SELECT @Startdate, @Enddate -- Sanity check; yes, it's Jan 1 to Jan 10

 SET STATISTICS TIME ON

PRINT '----- Orion Pax''s method 1 ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE CONVERT(VARCHAR(10), [TestDate], 111) BETWEEN @Startdate AND @Enddate -- VARCHAR(max)??

PRINT '----- Orion Pax''s method 2 ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE DATEADD(dd, DATEDIFF(dd, 0, [TestDate]),0) BETWEEN (@Startdate) and (@Enddate) 

PRINT '----- Jack''s method ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE TestDate >= @Startdate and TestDate < @Enddate+1

 SET STATISTICS TIME OFF

----- Orion Pax's method 1 ----------------
(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 413 ms,  elapsed time = 413 ms.

----- Orion Pax's method 2 ----------------
(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 24 ms.

----- Jack's method ----------------
(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SELECT COUNT(*)
FROM #DateTest
WHERE TestDate >= @Startdate and TestDate < DATEADD(ss, -1, @Enddate+1)

SELECT COUNT(*)
FROM #DateTest
WHERE TestDate BETWEEN @Startdate and DATEADD(ss, 86399, @Enddate)

DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0)

DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0) = @InputDate