Interval year to month - What is the point?
Posted on 2003-11-26
I have a question relating TO the interval datatypes IN Oracle 9i. Basically what is the point of the interval year to month? You see, all of the examples I've found relating to computing the difference between two dates using the interval year to month
data type have been to calculate someones age, or find the length of service of an employee, using something similar to the below query:
SELECT EXTRACT(YEAR FROM ((TO_TIMESTAMP('01/01/1990', 'DD/MM/YYYY') - TO_TIMESTAMP('16/01/1980', 'DD/MM/YYYY')) YEAR TO MONTH))
However, if you actually run the above query, it will return 10 years (of service), when in fact it should be 9 years, 11 months...
(if you extract months it returns 0). So I can't really see the point in using this as it will return incorrect results (as in the
length of service example, the employee would be down as 10 years and would possibly get a greater bonus than if they had done 9).
I know as an alternative you can use the below, which works fine (I have no problems with the day TO second):
SELECT FLOOR(EXTRACT(DAY FROM ((TO_TIMESTAMP('01/01/1990', 'DD/MM/YYYY') - TO_TIMESTAMP('16/01/1980', 'DD/MM/YYYY')) DAY TO SECOND)) / 365.25)
But this seems considerably more complex than the pre-9i way (AND what I would still USE) OF:
SELECT FLOOR(MONTHS_BETWEEN(TO_DATE('01/01/1990', 'DD/MM/YYYY'), TO_DATE('16/01/1980', 'DD/MM/YYYY')) / 12)
Oh, and as a further point, the following returns the correct value of 16/01/1990 for 10 years service, and 16/01/1989 for 9 years
SELECT to_timestamp('16/01/1980', 'DD/MM/YYYY') + INTERVAL '9' YEAR