DATE to NUMBER / pseudo-number convertion

Question

To my own surprise, I don't understand how it works:

SELECT NVL2 (null, SYSDATE - (SYSDATE - 1), SYSDATE)
  FROM dual;

How and why Oracle succeffully converts DATE to pseudo-number (Typ=14)? I've read NVL2, Numeric Precedence and Implicit Data Conversion documentation, but still have no idea how it works. There is -- in implicit type conversion table.

Answer 1

This is the query:

SELECT NVL2 (null, SYSDATE - (SYSDATE - 1), SYSDATE) result from dual;

NVL2 (from documentation):

• If expr1 is not null, then NVL2 returns expr2.
• If expr1 is null, then NVL2 returns expr3. >> your query will return EXPR3

Next:

• if expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

As expr2 really is numeric (result is 1); you don't even need database involved into it, it says

sysdate - (sysdate - 1) = sysdate - sysdate + 1 = 1

then expr3 is also implicitly converted to number. sysdate, being a function that returns date datatype, is converted to Julian date.

SQL> SELECT NVL2 (null, SYSDATE - (SYSDATE - 1), SYSDATE) result from dual;

    RESULT
----------
2458770,44

SQL> SELECT to_char(sysdate, 'J') result from dual;

RESULT
-------
2458770

Makes sense?