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

Posted in: Technical Track

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;


SQL> select (trunc(sysdate) - to_date('2006-02-28','YYYY-MM-DD'))/365.242199*12  from dual;


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 — 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 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!




Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Database Specialist based in Sydney,Australia

2 Comments. Leave new

laurent schneider
January 5, 2007 10:57 am

well, this 31-day behaviour is documented

laurent schneider
January 5, 2007 11:02 am

I mean, it is only for the fractional part :
select months_between(date ‘2000-03-15’,date ‘2000-02-15’) from dual;

In the doc they say:
Oracle calculates the fractional portion of the result based on a 31-day month


Leave a Reply

Your email address will not be published. Required fields are marked *