June 2, 2010
I found this interesting example a couple of months ago in a book, and something just did not seem right with the example. What is the problem with the example?
select employee_id, first_name, last_name, (sysdate - hire_date)*86400 Emp_Length_Seconds, extract(year from (sysdate - hire_date) year to month) || ' years, ' || extract(month from (sysdate - hire_date) year to month) || ' months. ' Emp_Length_Readable from hr.employees;
I have not used the EXTRACT SQL function much, if at all, prior to seeing this example, but I sensed that something was wrong.
Here is a test script to possibly help you find the problem with the above:
SET PAGESIZE 2000
COLUMN T FORMAT A20
SET TRIMSPOOL ON
SPOOL datedelta.txt
WITH DATES AS (
SELECT
TO_DATE('01-JAN-2010','DD-MON-YYYY') + (ROWNUM-1) D
FROM
DUAL
CONNECT BY
LEVEL<=365)
SELECT
D1.D D1,
D2.D D2,
D2.D-D1.D DATE_DELTA,
extract(year from (D2.D-D1.D) year to month) || ' years, ' ||
extract(month from (D2.D-D1.D) year to month) || ' months. ' T
FROM
DATES D1,
DATES D2
WHERE
D1.D<D2.D;
SPOOL OFF
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:
D1 D2 DATE_DELTA T --------- --------- ---------- -------------------- 01-JAN-10 02-JAN-10 1 0 years, 0 months. 01-JAN-10 03-JAN-10 2 0 years, 0 months. 01-JAN-10 04-JAN-10 3 0 years, 0 months. 01-JAN-10 05-JAN-10 4 0 years, 0 months. 01-JAN-10 06-JAN-10 5 0 years, 0 months. 01-JAN-10 07-JAN-10 6 0 years, 0 months. 01-JAN-10 08-JAN-10 7 0 years, 0 months. 01-JAN-10 09-JAN-10 8 0 years, 0 months. 01-JAN-10 10-JAN-10 9 0 years, 0 months. ... 05-MAR-10 16-JUN-10 103 0 years, 3 months. 05-MAR-10 17-JUN-10 104 0 years, 3 months. 05-MAR-10 18-JUN-10 105 0 years, 3 months. 05-MAR-10 19-JUN-10 106 0 years, 3 months. 05-MAR-10 20-JUN-10 107 0 years, 4 months. 05-MAR-10 21-JUN-10 108 0 years, 4 months. 05-MAR-10 22-JUN-10 109 0 years, 4 months. ...
What is wrong with this SQL statement, and more importantly, how do we fix it?
Charles Hooper's Oracle Notes 
seems to me that this example is copied from Oracle Documentation, isn’t it ?
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements001.htm#SQLRF50992
haven’t a clue why
shows me one month difference between the 2nd Jan 2010 and 17th Jan 2010
However, how to define the “month-part” of the difference of two dates ?
Sokrates,
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.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/expressions009.htm#sthref1885
this syntax was new for me and I agree with you, that it is “incorrect” or “unexpected”
However, I’m still not sure how to fix it
do you expect the “month-part” of the difference of two dates, say d2 – d1, to be the largest integer n such that add_months(d1, n) is smaller than or equal to d2 ?
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.
Gary,
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:
SET PAGESIZE 2000 COLUMN T FORMAT A20 COLUMN MONTHS FORMAT 90.0000 COLUMN M FORMAT 90 COLUMN D FORMAT 90 SET TRIMSPOOL ON SPOOL datedelta.txt WITH DATES AS ( SELECT TO_DATE('01-JAN-2010','DD-MON-YYYY') + (ROWNUM-1) D FROM DUAL CONNECT BY LEVEL<=365) SELECT D1.D D1, D2.D D2, D2.D-D1.D DATE_DELTA, extract(year from (D2.D-D1.D) year to month) || ' years, ' || extract(month from (D2.D-D1.D) year to month) || ' months. ' T, MONTHS_BETWEEN(D2.D,D1.D) MONTHS, MOD(TRUNC(MONTHS_BETWEEN(D2.D,D1.D)),12) M, ROUND((MONTHS_BETWEEN(D2.D,D1.D)-TRUNC(MONTHS_BETWEEN(D2.D,D1.D)))*30.41667) D FROM DATES D1, DATES D2 WHERE D1.D<D2.D; SPOOL OFFObviously, if someone else finds something else wrong with the example from the book or documentation, I would like to see any issues that are found.
What is probably not quite right is that
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements001.htm#g196492
indicates that a date_col – date_col should evaluate to a number
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions057.htm#SQLRF51389
indicates that you can only extract a month from a date, timestamp or INTERVAL YEAR TO MONTH.
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:
I probably need to spend some more time working with the EXTRACT function.
To keep people thinking about the SQL statement at the start of this article, the documentation reference that I provided in the article for the EXTRACT function (http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions057.htm) includes the following SQL statement:
Let’s put the SQL statement to the test:
CREATE TABLE T7 (ORDER_DATE DATE); INSERT INTO T7 SELECT TO_DATE('01-JAN-2010','DD-MON-YYYY')+(ROWNUM-1) FROM DUAL CONNECT BY LEVEL<=300; COMMIT; SELECT EXTRACT(month FROM order_date) "Month", COUNT(order_date) "No. of Orders" FROM T7 GROUP BY EXTRACT(month FROM order_date) ORDER BY "No. of Orders" DESC; Month No. of Orders ----- ------------- 1 31 5 31 7 31 3 31 8 31 9 30 4 30 6 30 2 28 10 27That 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 31That worked exactly as I thought that it should.
Now back to the original question, what is wrong with that SQL statement? 🙂
(13) year to month
is not a valid SQL Expression
((TO_DATE(’03-MAR-2010′,’DD-MON-YYYY’)-TO_DATE(’18-FEB-2010′,’DD-MON-YYYY’))) year to month
is a valid SQL Expression
see
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/expressions009.htm#sthref1885
the difference in the brackets is **NOT** “evaluated at first” to a number
Gray is right in his analysis.
It helps me to think of it in these terms:
For:
select extract(month from ( dt1 – dt2 ) year to month) from dual;
Oracle calculate dt1 – dt2.
Oracle converts this number of days to a “interval year to month” value.
A “interval year to month” value cannot have a fractional month, so the month value is rounded.
So we do not get the same precision as months_between.
See http://forums.oracle.com/forums/thread.jspa?threadID=354651 for more discussion.
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:
WITH DATES AS ( SELECT TO_DATE('01-JAN-2010','DD-MON-YYYY') + (ROWNUM-1) D FROM DUAL CONNECT BY LEVEL<=365) SELECT D1.D D1, D2.D D2, D2.D-D1.D DATE_DELTA, to_char(EXTRACT(YEAR FROM D2.D) - EXTRACT(YEAR FROM d1.d)) || ' years, ' || to_char( EXTRACT(MONTH FROM D2.D) - EXTRACT(MONTH FROM D1.D)) || ' months. ' as t from DATES D1, DATES D2 WHERE D1.D<D2.D;but if I extract month and year in this way must also be aware that the difference between two dates is only indicative, in this example I have a difference of 71 days indicated by two months
Regards
Donatello,
That is certainly an improvement for the first day of the month:
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.)
Charles
we should ask:
1) What does extract?
2) What I want as a result?
Reply
1) from what I understand, it extracts the month from a date,
so for example, EXTRACT (MONTH FROM to_date (’01-Mar-2010 ‘)) = 3
then:
SELECT EXTRACT(MONTH FROM to_date('01-mar-2010')) AS month1_ext , EXTRACT(MONTH FROM to_date('05-jan-2010')) AS month2_ext from dual MONTH1_EXT MONTH2_EXT ---------------------- ---------------------- 3 1it 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 ???).
Regards
“1) from what I understand, it extracts the month from a date,”
not only from a date, but also from an interval.
yes, the meaning of my answer is that
apart from a date or a range returns a number