OraFAQ Forum: SQL & PL/SQL » MONTHS_BETWEEN function clarification

Home » SQL & PL/SQL » SQL & PL/SQL » MONTHS_BETWEEN function clarification

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MONTHS_BETWEEN function clarification [message #582398]

Thu, 18 April 2013 02:12

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Need clarification about months_between function ..

C:\>sqlplus/nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 18 13:20:43 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn scott@orcl
Enter password:
Connected.
SQL> select months_between('28-feb-2013','28-jan-2013') from dual;

MONTHS_BETWEEN('28-FEB-2013','28-JAN-2013')
-------------------------------------------
                                          1

SQL>
SQL> select months_between('28-feb-2013','29-jan-2013') from dual;

MONTHS_BETWEEN('28-FEB-2013','29-JAN-2013')
-------------------------------------------
                                 .967741935

SQL>
SQL> select months_between('28-feb-2013','30-jan-2013') from dual;

MONTHS_BETWEEN('28-FEB-2013','30-JAN-2013')
-------------------------------------------
                                 .935483871

SQL>
SQL> select months_between('28-feb-2013','31-jan-2013') from dual;

MONTHS_BETWEEN('28-FEB-2013','31-JAN-2013')
-------------------------------------------
                                          1

SQL>

when date is 29 or 30 jan, then result is less than 1, but when date is 28 or 31 jan then result is 1, why? if 28 is 1 then why 29 or 30 is less than 1?

[EDITED by LF: fixed topic title typo; was "add_months"]

[Updated on: Thu, 18 April 2013 02:44] by Moderator

Report message to a moderator

Re: add_months function clarification [message #582399 is a reply to message #582398]

Thu, 18 April 2013 02:18

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

MONTHS_BETWEEN documentation explains it.

(By the way, how come the topic title asks for ADD_MONTHS clarification, but you never used it in your example?)

Report message to a moderator

Re: months_between function clarification [message #582402 is a reply to message #582399]

Thu, 18 April 2013 02:41

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Littlefoot wrote on Thu, 18 April 2013 02:18

(By the way, how come the topic title asks for ADD_MONTHS clarification, but you never used it in your example?)

sorry for mistake, actually it will be months_between

[Updated on: Thu, 18 April 2013 02:42]

Report message to a moderator

Re: add_months function clarification [message #582403 is a reply to message #582402]

Thu, 18 April 2013 02:48

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And the same thing happens with ADD_MONTHS:

SQL> select add_months(to_date('27/02/2013','DD/MM/YYYY'),1) from dual;
ADD_MONTHS(TO_DATE(
-------------------
27/03/2013 00:00:00

1 row selected.

SQL> select add_months(to_date('28/02/2013','DD/MM/YYYY'),1) from dual;
ADD_MONTHS(TO_DATE(
-------------------
31/03/2013 00:00:00

1 row selected.

Regards
Michel

Report message to a moderator

Re: add_months function clarification [message #582405 is a reply to message #582403]

Thu, 18 April 2013 02:53

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I meant Oracle is aware of what a month is (see documentation pointed by Littlefoot).
This is not the case when you use standard INTERVAL datatype:

SQL> select to_date('27/02/2013','DD/MM/YYYY') + interval '1' month from dual;
TO_DATE('27/02/2013
-------------------
27/03/2013 00:00:00

1 row selected.

SQL> select to_date('28/02/2013','DD/MM/YYYY') + interval '1' month from dual;
TO_DATE('28/02/2013
-------------------
28/03/2013 00:00:00

1 row selected.

SQL> select add_months(to_date('31/01/2013','DD/MM/YYYY'),1) from dual;
ADD_MONTHS(TO_DATE(
-------------------
28/02/2013 00:00:00

1 row selected.

SQL> select to_date('31/01/2013','DD/MM/YYYY') + interval '1' month from dual;
select to_date('31/01/2013','DD/MM/YYYY') + interval '1' month from dual
                                          *
ERROR at line 1:
ORA-01839: date not valid for month specified

Regards
Michel

Report message to a moderator

Re: add_months function clarification [message #582406 is a reply to message #582405]

Thu, 18 April 2013 03:30

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

actually i need the following requirement.

I want to calculate month between 28-feb-13 and (28-jan-2013 and 31-jan-13). If date between 28-feb-13 and '28-jan-2013' is 1, again 31-feb-13 and '28-feb-2013' is 1, but 29-jan and 30-jan is fraction. I understand why it from the documentation. now i need all will 1, as if used add_months('29-jan-13',1) it return 28-feb-2013, but months_between('28-feb-2013','29-jan-2013') return less than 1.

How i get it?

Report message to a moderator

Re: add_months function clarification [message #582407 is a reply to message #582406]

Thu, 18 April 2013 03:52

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You then have to create your function implementing your own definition of a month.

Regards
Michel

Report message to a moderator

Re: add_months function clarification [message #582408 is a reply to message #582407]

Thu, 18 April 2013 03:58

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Thanks Michel..
So it can not possible with sql.

Report message to a moderator

Re: add_months function clarification [message #582410 is a reply to message #582408]

Thu, 18 April 2013 04:12

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is surely possible but you have to specify in details what you want for any case and not just the example you gave.
You have to make detailed and general specification of your need for all possible cases.

Regards
Michel

Report message to a moderator

Re: add_months function clarification [message #582414 is a reply to message #582406]

Thu, 18 April 2013 04:31

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

mmi78

... again 31-feb-13 and ...

Not very likely to happen.

Report message to a moderator

Re: add_months function clarification [message #582416 is a reply to message #582410]

Thu, 18 April 2013 04:43

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Michel Cadot wrote on Thu, 18 April 2013 04:12

This is surely possible but you have to specify in details what you want for any case and not just the example you gave.
You have to make detailed and general specification of your need for all possible cases.

Regards
Michel

Actually Michel i want the following
As add_months('29-jan-2013',1) return '28-feb-2013', so i need to calculate month between 28-feb-2013 and 29-jan-2013. And i want to get 1 as the previous add_months function return 28-feb-2013.
May be i can clear it. Any sql clue for this.

@littlefoot, sorry for again mistake.

Report message to a moderator

Re: add_months function clarification [message #582417 is a reply to message #582416]

Thu, 18 April 2013 04:45

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You quoted me but you didn't read me.
Forget any example, give specification that does not refer to any example.
Specify first, give an example after.

Otherwise I will give you something that works for 28 feb and you will tell me "yes but it does not for this date", I'll give you a solution for this latter and you wxill tell me...

regards
Michel

[Updated on: Thu, 18 April 2013 04:46]

Report message to a moderator

Re: add_months function clarification [message #582422 is a reply to message #582417]

Thu, 18 April 2013 05:11

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Michel Cadot wrote on Thu, 18 April 2013 04:45

You quoted me but you didn't read me.
Forget any example, give specification that does not refer to any example.
Specify first, give an example after.

Otherwise I will give you something that works for 28 feb and you will tell me "yes but it does not for this date", I'll give you a solution for this latter and you wxill tell me...

regards
Michel

My requirement is
if add_months(date1,n) return date2,
then i need to get n, when i calculate difference between date2 and date1.
I hope i can give my requirement clearly.

Report message to a moderator

Re: add_months function clarification [message #582426 is a reply to message #582422]

Thu, 18 April 2013 05:25

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If DD2=last day and DD1>=DD2 then ceil(months_between) else months_between:

SQL> def refdat=to_date('28/02/2013','DD/MM/YYYY')
SQL> with
  2    dates as (
  3      select to_date('25/01/2013','DD/MM/YYYY')+level dat
  4      from dual connect by level <= 7
  5    )
  6  select dat, months_between(&refdat, dat) diff,
  7         case
  8           when    &refdat = last_day(&refdat)
  9               and extract(day from dat) >= extract(day from &refdat)
 10             then ceil(months_between(&refdat, dat))
 11           else months_between(&refdat, dat)
 12         end new_diff
 13  from dates
 14  order by 1
 15  /
DAT                       DIFF   NEW_DIFF
------------------- ---------- ----------
26/01/2013 00:00:00 1.06451613 1.06451613
27/01/2013 00:00:00 1.03225806 1.03225806
28/01/2013 00:00:00          1          1
29/01/2013 00:00:00 .967741935          1
30/01/2013 00:00:00 .935483871          1
31/01/2013 00:00:00          1          1
01/02/2013 00:00:00 .870967742 .870967742

Regards
Michel

[Updated on: Thu, 18 April 2013 05:53]

Report message to a moderator

Re: add_months function clarification [message #582428 is a reply to message #582426]

Thu, 18 April 2013 05:50

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Thanks Michel.. great help for me.

Report message to a moderator

Previous Topic:	Why are FK not deferrable by default
Next Topic:	Open cursor statement getting stuck (V.interesting issue)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 18 02:39:12 CDT 2025