Posted by Plamen Ratchev on 02/28/07 16:15

In order to drop the time portion of a date via the CONVERT function you
have to do a double convert, first to character type and then to datetime.
Here is how it will look:

WHERE ( CONVERT(DATETIME, CONVERT(CHAR(10), tblESEnrollments.DTStamp, 101))
>= @StartDate) AND
( CONVERT(DATETIME, CONVERT(CHAR(10), tblESEnrollments.DTStamp,
101)) <= @StopDate)

However, a better approach is to use the DATEDIFF function as it will be
more efficient than converting and it will allow you to utilize any indexes
on the DTStamp column. Here is how it will look:

WHERE tblESEnrollments.DTStamp >= DATEDIFF(day, 0, @StartDate) AND
tblESEnrollments.DTStamp <= DATEDIFF(day, -1, @StopDate)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]