Skip to Main Content
  • Questions

  • Explain conversion rules used in NVL2

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pierre.

Asked: May 25, 2020 - 8:44 am UTC

Last updated: May 26, 2020 - 4:35 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Please explain the conversion rules used for this SQL statement (we found some rules https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/NVL2.html#GUID-414D6E81-9627-4163-8AC2-BD24E57742AE but could not apply in this case):


select NVL2( NULL, (SYSDATE - SYSDATE), DATE '2020-05-24' ) from dual
SQL> /

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

and Chris said...

The second argument (SYSDATE - SYSDATE) subtracts a date from a date. This returns a number.

So it does an implicit conversion of the third (date) argument to a number.

But... what's a numeric format for dates?!

Julian days - the number of days since January 1, 4712 BC.

The Julian days value for 24 May 2020 is 2,458,994:

select NVL2( NULL, (SYSDATE - SYSDATE), DATE '2020-05-24' ) from dual;

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

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

TO_CHAR(DATE'2020-05-24','J')   
2458994

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pierre, May 26, 2020 - 2:39 pm UTC

Thanks for your answer.

I would like to know if this conversion from date to number of Julan days is documented somewhere because I could not find it. If it does not exist, I consider this a documentation bug: do you agree ?


Chris Saxon
May 26, 2020 - 4:35 pm UTC

All I can find is:

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.

If you want an explicit sentence about dates converting to Julian days, raise a doc bug!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.