Why does this SQL statement work:

SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL

and this one does not work?

SELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL

I understand that you cannot operate a number with a date, but because of this, I don't understand why the first query works.

CodePudding user response：

SYSDATE - 1 means one day before now. Oracle sees the attempt to substract (or add) a number to/from a date and automatically interprets the number as a full 24-hour day. That's why it works.

You cannot however reverse this: 1 - SYSDATE is meaningless in Oracle. It would have to mean "now before one day" which makes no sense. You can subtract/add numbers (days) to dates, but you cannot subtract a date from a number.

CodePudding user response：

SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL

Works because the operators are evaluated in precedence order and then, for operators of the same precedence, from left-to-right so the query is, with brackets to show the order of evaluation:

SELECT ((SYSDATE - 1) - DATE '2019-01-01') FROM DUAL

And the inner bracket has the types DATE - NUMBER which results in a DATE type and then substituting that into the outer bracket, it has the types DATE - DATE which outputs NUMBER; so the query works.

SELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL

Can be rewritten with brackets showing the evaluation order of operators as:

SELECT ((1 - SYSDATE) - DATE '2019-01-01') FROM DUAL

Which fails because 1 - SYSDATE is NUMBER - DATE which is not a valid operation.

If you want it to be syntactically correct then use brackets to change the precedence:

SELECT 1 - (SYSDATE - DATE '2019-01-01') FROM DUAL

Then SYSDATE - DATE '2019-01-01' is evaluated first and DATE - DATE = NUMBER and then NUMBER - NUMBER will work.