Home » SQL & PL/SQL » SQL & PL/SQL » MONTHS_BETWEEN function clarification
MONTHS_BETWEEN function clarification [message #582398] Thu, 18 April 2013 02:12 Go to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior 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 Go to previous messageGo to next message
Littlefoot
Messages: 19345
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?)
Re: months_between function clarification [message #582402 is a reply to message #582399] Thu, 18 April 2013 02:41 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior 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 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: add_months function clarification [message #582405 is a reply to message #582403] Thu, 18 April 2013 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: add_months function clarification [message #582406 is a reply to message #582405] Thu, 18 April 2013 03:30 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior 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?
Re: add_months function clarification [message #582407 is a reply to message #582406] Thu, 18 April 2013 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You then have to create your function implementing your own definition of a month.

Regards
Michel
Re: add_months function clarification [message #582408 is a reply to message #582407] Thu, 18 April 2013 03:58 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
Thanks Michel..
So it can not possible with sql.


Re: add_months function clarification [message #582410 is a reply to message #582408] Thu, 18 April 2013 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: add_months function clarification [message #582414 is a reply to message #582406] Thu, 18 April 2013 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mmi78
... again 31-feb-13 and ...

Not very likely to happen.
Re: add_months function clarification [message #582416 is a reply to message #582410] Thu, 18 April 2013 04:43 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior 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.
Re: add_months function clarification [message #582417 is a reply to message #582416] Thu, 18 April 2013 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior 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.

Re: add_months function clarification [message #582426 is a reply to message #582422] Thu, 18 April 2013 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
Thanks Michel.. great help for me.
Previous Topic: Why are FK not deferrable by default
Next Topic: Open cursor statement getting stuck (V.interesting issue)
Goto Forum:
  


Current Time: Thu Jul 31 19:03:17 CDT 2014

Total time taken to generate the page: 0.05552 seconds