How does ORACLE database convert DATE to NUMBER implicitly?

user_3259807 0
Viewed 846

I'm trying to understand this function:

NVL2( NULL, ( SYSDATE - SYSDATE ), DATE '2020-05-24' ))

And its returned value:

NVL2(NULL,(SYSDATE-SYSDATE),DATE '2020-05-24'))

2458994

I'm having trouble understanding where that number, 2458994, comes from, as SYSDATE-SYSDATE is a NUMBER, and you cannot implicity convert a DATE to a NUMBER:

TO_NUMBER(DATE '2020-05-24')
ORA-01722: invalid number

The ORACLE SQL Language Reference NVL2 states:

If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

So my question is, what form of conversion is ORACLE SQL using on the DATE datatype to make it a NUMBER datatype?

oracle sql
1 Answers

It's effectively doing:

to_number(to_char(DATE '2020-05-24','J'))

The 'J' is (from the docs):

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

If you run that manually it gets the same value you see:

select to_number(to_char(DATE '2020-05-24','J')) from dual;

2458994

It isn't obvious that it should be doing that, but it is. If the second argument is a plain (type-2) number then you get an error:

select NVL2(NULL,42,to_date('2020-05-24','YYYY-MM-DD')) from dual;

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

If you dump the date subtraction result it comes back as a different (internal, undocumented as far as I can see) data type:

select dump(SYSDATE-SYSDATE) from dual;

Typ=14 Len=8: 114,133,37,0,0,0,0,0

That seems to cause the third argument to be converted to that same type; it's almost equivalent to:

select DATE '2020-05-24' - DATE '-4712-01-01' from dual;

2458993

So it looks like it's either doing something similar to that but adjusting it, or doing an internal version of the 'J' conversion, or... something else vaguely similar. It doesn't seem to be documented behaviour.

user_2663040
Related