Posted by Wes Peters on 02/28/07 14:13

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

• Next in forum: Does anyone use SSIS for database schema maintenance?
• Prev in forum: Re: return duplicate records
• Thread view: Need help with CONVERT function

[Reply to this message]