Oracle Gotcha: Months_between 31-days-per-month assumption

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