SQL Error using CASE statement

[Ted Young]

I am trying to run the SQL below and I am getting an error that states:

INCORRECT SYNTAX NEAR '>'

Does anyone have any ideas on what could be wrong?

UPDATE tblAgentStatusHistory
SET EffectiveDate =
CASE EffectiveDate
WHEN EffectiveDate > '12/1/1997' THEN '06/28/1968'
ELSE '06/28/2001'
END
WHERE AgentID = '000528612'

Thanks!

Ted

[DaveSatz]

try:
UPDATE tblAgentStatusHistory
SET EffectiveDate = (
CASE
WHEN EffectiveDate > '12/1/1997' THEN '06/28/1968'
ELSE '06/28/2001'
END )
WHERE AgentID = '000528612'
--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b584394$1@news.devx.com...
>
> I am trying to run the SQL below and I am getting an error that states:
>
> INCORRECT SYNTAX NEAR '>'
>
> Does anyone have any ideas on what could be wrong?
>
> UPDATE tblAgentStatusHistory
> SET EffectiveDate =
> CASE EffectiveDate
> WHEN EffectiveDate > '12/1/1997' THEN '06/28/1968'
> ELSE '06/28/2001'
> END
> WHERE AgentID = '000528612'
>
> Thanks!
>
> Ted