ORA-00904: "DATEDIFF": invalid identifier

Toad for Oracle
1

Hello folks,
I have the following statement in my Select section of my program:

SUM(
CASE
WHEN work_day_flag = 'Y' AND DATEDIFF('day', tranc(a.ACTUAL_DATE), nest1.LAST_WORK_DAY_IN_MONTH) < 7
THEN (7- DATEDIFF('day', trunc(a.ACTUAL_DATE), b.LAST_WORK_DAY_IN_MONTH)) / 5
ELSE 0
END
) AS ENDMNTHWRKDAYSWGTD,

I am getting ORA-00904: "DATEDIFF": invalid identifier error- Can you please help me solving this issue?

Thank you - Gabe

2

DATEDIFF is not an Oracle function.

It looks like you want to find the number of days between those two values (and you don't care which one comes first)

Something like this should do the same.

abs(trunc(a.ACTUAL_DATE) - trunc(nest1.LAST_WORK_DAY_IN_MONTH)) < 7

3

Thank you John, I would never have known that.

4

HI John, You just saved my weekend from being miserable weekend.
Thank you.
Gabe

5

You're welcome.