MONTHS_BETWEEN function does not give the expected result

Question

I am using MONTHS_BETWEEN function to get the difference between 2 dates , but the result is not 100% correct i think this function mines 1 day . i tried this SQL

 SELECT MONTHS_BETWEEN(TO_DATE('20170630','YYYYMMDD'),
   TO_DATE('20170501','YYYYMMDD') ) "Months"
    FROM DUAL;

here in this case it should return 2 but the result is 1.93548387096774 So, Any help ?? or is there any way to add 1 more day to date??

If you want to add 1 more day to date you can use `DATEADD (datepart , number , date ) ' — OLIVER.KOO, Jul 05 '17 at 04:17
See similar Questions: [*Months between not returning correct value*](https://stackoverflow.com/q/46070595/642706) & [*Analog of ORACLE function MONTHS_BETWEEN in Java*](https://stackoverflow.com/q/9100543/642706) — Basil Bourque, Feb 02 '18 at 00:17

score 0 · Answer 1 · answered Jul 05 '17 at 03:55

0

Try

SELECT ROUND( MONTHS_BETWEEN(
  TO_DATE('20170630','YYYYMMDD'), 
  TO_DATE('20170501','YYYYMMDD'))) 
"Months" FROM DUAL;

MONTHS_BETWEEN returns decimal result if days are different and they are not both specify the last day of the month. For more info see http://www.sqlines.com/oracle/functions/months_between

answered Jul 05 '17 at 03:55

VladimirAus

181
1
4

IT will not work round here – Ghalib Ali Jul 05 '17 at 04:04
It works in Oracle emulator here: http://sqlfiddle.com/#!4/0d890/3 – VladimirAus Jul 05 '17 at 04:23
You get sqlfiddle to work? It always fails when I try. Pretty much given up on that and use this one instead for Oracle: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ec153e296b0880c80515b8e50471ec0d – Paul Maxwell Jul 05 '17 at 04:26
Works as well: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e62865daa3b3e0d27d27753a37cee925 – VladimirAus Jul 05 '17 at 04:35
Guess the problem is not that if it works or not. `ROUND` will give you result 2 even from `11th May` to `30th June` which is not what OP wants. Question is more specific to `months_between` that why from `1st May` to `30th June` it is not giving `2` and to get it , you should add 1 day to last_date. – Utsav Jul 05 '17 at 04:40

Utsav · Answer 2 · 2017-07-05T04:42:37.830

Months between will give you a decimal value with respect to a whole month. So from 1st Jan to 1st Feb, you will get 1. But from 1st Jan to 31st Jan you will not get exact 1. It will be 0.9.... Similarly from 31st Jan to 28th Feb (non leap year) you will get 1.

So you will get 2 from 1st May to 1st July OR from 30th April to 30th June,

From 1st May to 30th June, you will never get exact 2.

If you want exact 2, then either add 1 day to greater date or subtract 1 day from start_date.

DBFiddle Demo

MONTHS_BETWEEN function does not give the expected result

2 Answers2