Need help with CONVERT function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wes Peters - 28 Feb 2007 14:13 GMT

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

Reply to this Message

Plamen Ratchev - 28 Feb 2007 16:15 GMT

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

Reply to this Message