Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh