CODEWITHSUNDEEP
sqloracle-database
arccuks
arccuks

Reputation: 173

Oracle function cant pass in date - DAYS

I tried to pass date - DAYS to Oracle Function, but i kept getting error.

This one working fine :

SELECT
    TRUNC(SYSDATE) - 730 DAYS
FROM
    dual;

This one returns error: ORA-00907: missing right parenthesis

SELECT
    MYFUNCTION(TRUNC(SYSDATE) - 730 DAYS)
FROM
    dual;

When i change this to another SELECT, it works, but why is that so ?? I really like the one above, since its easier + if I need to change it to MONTH or YEAR or whatever, its easy. But it doesn't work.

SELECT
    MYFUNCTION(
        (
        SELECT
            TRUNC(SYSDATE) - 730 DAYS
        FROM
            dual
        )
    )
FROM
    dual;

Upvotes: 1

Views: 463

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You mistakenly think you are dealing with an interval 730 DAYS. But

SELECT TRUNC(SYSDATE) - 730 DAYS FROM dual;

is

SELECT TRUNC(SYSDATE) - 730 AS DAYS FROM dual;

So you calculate TRUNC(SYSDATE) - 730 where Oracle takes 730 to mean 730 days, as this is how adding or subtracting a number from a date is defined, and then you call this result column "DAYS".

This explains why

SELECT MYFUNCTION(TRUNC(SYSDATE) - 730 DAYS) FROM dual;

results in a syntax error; you are using an alias name ("DAYS") where it doesn't belong.

Make this

SELECT MYFUNCTION(TRUNC(SYSDATE) - 730) FROM dual;

or

SELECT MYFUNCTION(TRUNC(SYSDATE) - INTERVAL '730' DAY(3)) FROM dual;

instead.

Upvotes: 1

Related Questions