Hi Tom,
While I was working through an example in a book, I ran into a problem I couldn't figure out.
It involves two scripts and its results which are provided below:
Script 1:
SELECT loan_id,due_date, tool_out_date,
NUMTOYMINTERVAL(due_date - tool_out_date, 'YEAR') "Years"
from loan;
Result Set 1:
loan_id | due_date | tool_out_date | Years
-------------------------------------------
7 17-02-11 17-02-04 +07-00
8 17-01-05 17-01-02 +03-00
Script 2:
SELECT loan_id,due_date, tool_out_date,
NUMTODSINTERVAL(due_date - tool_out_date, 'DAY') "Days"
from loan;
Result Set 2:
loan_id | due_date | tool_out_date | Days
-------------------------------------------------------
7 17-02-11 17-02-04 +07 00:00:00.000000
8 17-01-05 17-01-02 +03 00:00:00.000000
Based off of these scripts and the result sets, result set 2 makes a lot of sense as it returns the interval of days in between the two dates. I don't understand how or why script 1 is not able to return the right value for years which I'm guessing would be 0 since both rows occur in 2017? It instead returns values that show the interval as being 7 years and 3 years respectively.
Any help would be appreciated in helping me understand why this happens.
Thank You.
NUMTOYMINTERVAL is a *constructor* function, ie, it used to *build* an interval.
So if I want an interval of 4 years, I issue:
NUMTOYMINTERVAL(4,'YEAR')
So in your case, we are:
1) evaluating 'due_date - tool_out_date' which returns a number datatype
2) passing that number into the constructor
It "works" for NUMTODS because of a "coincidence" that date subtraction returns the number of *days*, and you are using 'DAY' in numtodsinterval. FOr example, if you did:
NUMTODSINTERVAL(due_date - tool_out_date, 'SECOND') you will NOT get the number of seconds, you will get the '7' seconds (just like you got 7 years).
See this like for a mean of deriving years between 2 dates
https://asktom.oracle.com/pls/asktom/asktom.search?tag=calculating-age-in-terms-of-yearsmonths-and-days