Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of everyday DBA life.
Dear Diary,
Do you want to know a secret? Understanding it will prevent pain and gnashing of teeth, and also leave your face comfortably free of egg.
Here it is: Some Oracle functions assume there are 31 days for each month of the year!
Check out the fractional difference between the function MONTHS_BETWEEN which uses a 31 day month for all months and using the underlining Oracle DATE based on days:
SQL> select months_between(trunc(sysdate),to_date('2006-02-28','YYYY-MM-DD')) from dual;
MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE('2006-02-28','YYYY-MM-DD'))
-----------------------------------------------------------------
7.25806452
SQL> select (trunc(sysdate) - to_date('2006-02-28','YYYY-MM-DD'))/365.242199*12 from dual;
(TRUNC(SYSDATE)-TO_DATE('2006-02-28','YYYY-MM-DD'))/365.242199*12
-----------------------------------------------------------------
7.19522554
The moral of the story: be careful when using MONTHS_BETWEEN for fractional dates, e.g. when using the output to go back to days.
Also, if you use a managed standby with a DELAY, which is not the latest version — i.e. less than 9.2.0.6 — there is a nasty bug related to a similar assumption, which will break the standby. So just when you thought your standby solution was robust and free of issues…
See metalink DocID 280909.1. You can fix this bug by patching to 9.2.0.6 or above, or by upgrading to a higher release such as 10G.
Update 2007/09/10: Dominic over at Orastory has a nice story about the INTERVAL function as well
Have Fun!
Paul
2 Comments. Leave new
well, this 31-day behaviour is documented
I mean, it is only for the fractional part :
select months_between(date ‘2000-03-15’,date ‘2000-02-15’) from dual;
1
In the doc they say:
Oracle calculates the fractional portion of the result based on a 31-day month