Home » SQL & PL/SQL » SQL & PL/SQL » Date difference in sql
Date difference in sql [message #186665] Tue, 08 August 2006 23:35 Go to next message
Ankur Bhatia
Messages: 14
Registered: May 2006
Location: India
Junior Member

Hello friends,

i want to calculate the difference between the two dates. i wrote a query:
select months_between(:d2,:d1)/12 from dual;

and the outpur that comes is:
8.84677419354839

but i need an output as:
8 years 10 months 5 days

what can i do, please help its very urgent....

Thanks in Advance,
Ankur
Re: Date difference in sql [message #186671 is a reply to message #186665] Wed, 09 August 2006 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look here
Just an advice: if it is so very urgent, you might want to search the net for it!
icon14.gif  Re: Date difference in sql [message #186675 is a reply to message #186665] Wed, 09 August 2006 00:38 Go to previous message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi Ankur,

The query to your issue is given below, i have used 23-JUL-2001 and 01-JAN-2000 as sample dates,

SELECT    FLOOR (MONTHS_BETWEEN ('23-JUL-2001', '01-JAN-2000') / 12)
       || ' Years '
       || FLOOR (MONTHS_BETWEEN ('23-JUL-2001', '01-JAN-2000'))
       || ' Months '
       || (  TO_DATE ('23-JUL-2001', 'DD-MON-RRRR')
           - TO_DATE
                 (ADD_MONTHS (TO_DATE ('01-JAN-2000', 'DD-MON-RRRR'),
                              FLOOR (MONTHS_BETWEEN (TO_DATE ('23-JUL-2001',
                                                              'DD-MON-RRRR'
                                                             ),
                                                     TO_DATE ('01-JAN-2000',
                                                              'DD-MON-RRRR'
                                                             )
                                                    )
                                    )
                             ),
                  'DD-MON-RRRR'
                 )
          )
       || ' Days' AS RESULT
  FROM DUAL


Thanks,
Vinod V
Previous Topic: TKPROF
Next Topic: percentage calculations
Goto Forum:
  


Current Time: Fri Dec 09 09:24:22 CST 2016

Total time taken to generate the page: 0.09635 seconds