OraFAQ Forum: SQL & PL/SQL » Date difference in months

Home » SQL & PL/SQL » SQL & PL/SQL » Date difference in months

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

Date difference in months [message #9827]

Sat, 06 December 2003 10:19

Mantas
Messages: 1
Registered: December 2003

Junior Member

Hello,

I need to get such difference in months. Example: if date1 is 2003.10.01 and date2 is 2003.10.01 i need to get difference 1.
And if date1 is 2003.07.01 and date2 is 2003.09.01 i need to get difference in months equal 3.
How could i write it?
Thanks.

Report message to a moderator

Re: Date difference in months [message #9840 is a reply to message #9827]

Mon, 08 December 2003 01:34

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How about this:

SQL> Select months_between(trunc(sysdate,'MM'), trunc(hiredate,'MM')) mb
  2       , hiredate
  3       , trunc(hiredate,'MM')
  4       , trunc(sysdate,'MM')
  5    from emp
  6  /

        MB HIREDATE    TRUNC(HIRED TRUNC(SYSDA
---------- ----------- ----------- -----------
       265 17-NOV-1981 01-NOV-1981 01-DEC-2003
       271 01-MAY-1981 01-MAY-1981 01-DEC-2003
       270 09-JUN-1981 01-JUN-1981 01-DEC-2003
       272 02-APR-1981 01-APR-1981 01-DEC-2003
       267 28-SEP-1981 01-SEP-1981 01-DEC-2003
       274 20-FEB-1981 01-FEB-1981 01-DEC-2003
....

The dates are truncated by month. MONTHS_BETWEEN gives exactly what you need. But the months between you calculate are actually a month too high.

MHE

Report message to a moderator

Previous Topic:	char data type --->display string
Next Topic:	Reg:Schedules.......

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 04:32:07 CDT 2024