Need help with CONVERT function

[Wes Peters]

I have a situation where I want to filter records for a given day. The
field that stores the date/time uses the date() function as a default value,
kind of a date/time stamp for the record.

The problem is when I want to filter records for a given day instead of a
date range. I use the CONVERT function to return just the date part of the
field (101 as a style parameter) and compare that to a start and stop date
(both being the same) and I get nothing. The stored procedure is as
follows:

Alter Procedure spESEnrollmentCount
@StartDate smalldatetime, @StopDate smalldatetime
As
SELECT tblCustomers.CustomerName, tblCostCenters.CostCenter,
COUNT(tblESEnrollments.EnrollmentID)
AS [Count of Enrollments]
FROM tblESEnrollments
INNER JOIN tblCustomers ON tblESEnrollments.CustID = tblCustomers.CustID
INNER JOIN tblCostCenters ON tblCustomers.CostCenterID =
tblCostCenters.CostCenterID
WHERE ( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) >= @StartDate) AND
( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) <= @StopDate) AND
(Rejected = 0)
GROUP BY tblCustomers.CustomerName, tblCostCenters.CostCenter

If I put 10/31/06 in for both parameters shouldn't I get records dated
10/31/06 if there are some?

Thanks,

Wes

[Plamen Ratchev]

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