The above script scolls through every start and end date combination for this year, writing a modified version of the calculation from the original SQL statement to a text file. The output should look something like this:

Thank you for providing the link to the documentation – the example from the book does look very similar to the documentation. One of my concerns is that this example may lead to “incorrect” results or “unexpected” results as you have illustrated with your example.

Are you just talking about rounding, where 14 days is ’rounded’ down to 0 months and 15 days is ’rounded’ up to 1 month. Or vaguely around 14/15 days. If the solar system had been sensibly specified, then the rotation of the earth and the duration of its orbit around the sun would be a whole number ratio, preferably with some decent factors for each breakdown.

You can use cast((D2.D-D1.D)/(30.4) as number(3,0)) DATE_DELTA_months to see a similar rounding happening with numerics.

I am looking for anything that is not quite right with the demonstration. The book page states:
“For instance, we can use the next SQL statement to find the employment duration for employees in the HR.EMPLOYEES table, but then have to wrestle with part-days represented as fractional numbers…
The next SQL statement achieves both precise seconds and human-readable years and months as output.”

The SQL statement at the start of this blog article is then shown. Now consider a programmer reading that section of the book, or the documentation reference provided by Sokrates (seems to be down right now just like the documentation reference I provided). The programmer is asked to find the employee’s three month anniversary (start of insurance) and the one year anniversary (vacation time roll-over). Given the description, would the programmer expect the EXTRACT code to return a result similar to MONTHS_BETWEEN? For example:

Even if it was doing an implicit conversion of the dates to a timestamp, the timestamp subtraction would give an INTERVAL DAY TO SECOND which still isn’t valid.

In your example, you are explicitly doing a TRUNC. If you CAST(0.5161 AS NUMBER(2,0)) you get 1. You see the same if simply insert 0.51into a column defined as NUMBER(2,0) . Oracle implicity ROUNDs rather than TRUNCs. If you are looking for a precision of 1 day, then INTERVAL YEAR TO MONTH isn’t exact enough. And if you are looking for ‘month/year’ arithmetic, INTERVAL DAY TO SECOND doesn’t really work either. It might be ANSI standard, but it doesn’t mean its useful.

The first documentation reference that you provided is the exact reason why I initially marked this SQL statement as being wrong. Assume that subtracting the second date from the first date results in the numeric value of 13 (just to keep things simple). The first question is “What is the assumed year?” – is it this year (2010), 2008, 1970, 1900, 1902, etc. The second question is “What is the assumed month” – is it January, February, March, etc. The third question is “What is the assumed day of the month for the zero point starting date?” – is it today’s day of the month, the first day of the current month, the first day of January, etc. What if the starting date and ending date span multiple years (the birth date and death date of someone famous, for example)?

Back to the example of the numeric value 13. Something odd is going on with this EXTRACT function. In this case, the result of subtracting a date from a date is not a number:

SELECT
extract(year from (13) year to month) || ' years, ' ||
extract(month from (13) year to month) || ' months. ' T
FROM
DUAL;
ERROR at line 2:
ORA-30083: syntax error was found in interval value expression
SELECT
(TO_DATE('03-MAR-2010','DD-MON-YYYY')-TO_DATE('18-FEB-2010','DD-MON-YYYY')) DAYS,
extract(year from ((TO_DATE('03-MAR-2010','DD-MON-YYYY')-TO_DATE('18-FEB-2010','DD-MON-YYYY'))) year to month) || ' years, ' ||
extract(month from ((TO_DATE('03-MAR-2010','DD-MON-YYYY')-TO_DATE('18-FEB-2010','DD-MON-YYYY'))) year to month) || ' months. ' T
FROM
DUAL;
DAYS T
---------- --------------------
13 0 years, 1 months.
SELECT
(TO_DATE('02-MAR-2008','DD-MON-YYYY')-TO_DATE('18-FEB-2008','DD-MON-YYYY')) DAYS,
extract(year from ((TO_DATE('02-MAR-2008','DD-MON-YYYY')-TO_DATE('18-FEB-2008','DD-MON-YYYY'))) year to month) || ' years, ' ||
extract(month from ((TO_DATE('02-MAR-2008','DD-MON-YYYY')-TO_DATE('18-FEB-2008','DD-MON-YYYY'))) year to month) || ' months. ' T
FROM
DUAL;
DAYS T
---------- --------------------
13 0 years, 0 months.

The number 13 causes the SQL statement to fail. In the second of the above SQL statements, if Oracle assumed that 13 was in January of any year, months should evaluate to 0, but Oracle returned a value of 1 month. In the third of the above SQL statements, if Oracle assumed that 13 was in February of this year, like the second example, then Oracle should have returned a value of 0 months.

Another interesting result was provided in the code section of my previous response. It appears that Oracle is not strictly rounding the results of “extract(month from (D2.D-D1.D) year to month)” using the same calculation as used by the MONTHS_BETWEEN function. If that was the case, the date range of 01-JAN-2010 through 16-JAN-2010 would be rounded down to 0 months, but 16-JAN-2010 is indicated as the start of 1 month:

That worked exactly as expected, but are we really seeing the 31 days that fall into January, or is Oracle rounding and stealing part of the days from February (the previous tests show that this is not the way the rounding was working – the days would actually be stolen from January and given to Feburary). Now to verify:

SELECT EXTRACT(month FROM order_date) "Month",
COUNT(order_date) "No. of Orders"
FROM T7
WHERE
ORDER_DATE BETWEEN TO_DATE('01-JAN-2010','DD-MON-YYYY') AND TO_DATE('31-JAN-2010','DD-MON-YYYY')
GROUP BY EXTRACT(month FROM order_date)
ORDER BY "No. of Orders" DESC;
Month No. of Orders
----- -------------
1 31

That worked exactly as I thought that it should.

Now back to the original question, what is wrong with that SQL statement? :-)

Hi Charles,
I think I used only twice EXTRACT, perhaps in its simplest form, honestly I don’t know it very well.
curious :) , I did a test and I think that a possible solution to this problem
could be the following:

It appears that the solution you provided, and possibly any solution using EXACT, still has issues. For example, if you check January 5, you will see that the number of months advances when February 1 appears for the D2 column.

It might simply be a problem with the precision of the EXACT functionality, as mentioned in the comment by Gary.

(Edit: In the comment I originally described how the number of months advances when January 5 is the start date (column D1) and February 1 appears in the D2 column. Unfortunately, the code output section of the comment showed what happened when March 1 appeared in the D2 column. I added the January 5/February 1 output to the code output section in the comment.)

it does is simply, 3 – 1 = 2, then in my opinion should not be
used when we want the difference between two dates.

2)
if I have to do a temporal analysis on the data expressed in month, I have two situations,
I think. Compare fixed dates (eg the first of each month), or to compare between any dates.
In the first case, I should not have problems using Extract. In the second case, should be
established a rule. Example: from 1 to 15 of every month as the first of this month, from
16 onwards consider the first of next month.In the absence of rules, I do not think you can
use Extract and I do not think it makes sense to have a difference between two
dates in months(1.5 months is a month or two months ???).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: