SQL2000 DateDiff Syntax Error

[Rock6431]

I am having probs with the Datediff listed below. I have tried "d", "dd", d, dd, the datediff with and without the outside parentheses and I continue to get invalid syntax.

I used QA and it reflects the proper use of &quot;(DateDiff(dd, Tdate, DueDate) < 0 )&quot; in a where statement. However, when I move it to a WHEN clause I can not get it to work correctly.

Basically, I have &quot;DueDate&quot; and &quot;Tdate&quot;. DueDate is given, while Tdate is the date the entry is made into the database. I am wanting to compare the DueDate to the Tdate to determine at the time the entry was made into the Database, was the item pastdue. If so, then count it into the total DueDates so that I can get a count of PastDue items.

Listed below is the query and error statement.

Many Thanks in Advance,
Rock6431



SELECT
Vreject AS RejectCode,
SUM(CASE DueDate
WHEN (DateDiff(dd, Tdate, DueDate) < 0 ) THEN 1
ELSE 0
END) AS PASTDUE
FROM IWworklist
WHERE dbo.DATEPART(Tdate) = &quot;01/08/2003&quot;
GROUP BY Vreject
ORDER BY Vreject

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '<'.
Stored Procedure: RAMPIT.iso\mschoenr.IWworklist_Due_Daily
Return Code = 0

 

[sunila7]

hi,

Try this

SELECT
Vreject AS RejectCode,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) < 0 THEN 1
ELSE 0
END) AS PASTDUE
FROM IWworklist
WHERE Tdate = '01/08/2003'
GROUP BY Vreject
ORDER BY Vreject


Sunil

 

[Rock6431]

Thanks alot. The DueDate after case was throwing me off. Much appreciated... Rock6431