Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate year, month & days between to date's
How to calculate year, month & days between to date's [message #1862] Wed, 05 June 2002 21:16 Go to next message
Chand
Messages: 3
Registered: June 2002
Junior Member
Can any one explain how to get the result
like follows;

a := date1
b := date2

i want b - a = yy yrs, mm mts , dd days
e.g 20yrs 02mts 12days

i already tried with this query

Select to_char(floor(months_between(sysdate,dob)/12)),
mod(round(months_between(sysdate,dob)),12)
from table_name

In the above query, when rounded month reach the value of 12 the year is not getting incrementing.

thanks in advance..
chand
Re: How to calculate year, month & days between to date's [message #1865 is a reply to message #1862] Wed, 05 June 2002 22:07 Go to previous messageGo to next message
Maaher
Messages: 7061
Registered: December 2001
Senior Member
Time differences are measured in units of days:
d1,d2 (date)
d1-d2 = result (days between d1 and d2)

to calculate the number of hours --> result*24
to calculate the number of minutes --> result*24*60
etc...

If you substract the number of complete days from your result and multiply this by 24, you'll get the remaining hours.

There are several threads in the forum covering this issue.

MHE
Re: How to calculate year, month & days between to date's [message #1874 is a reply to message #1862] Thu, 06 June 2002 22:03 Go to previous messageGo to next message
Maaher
Messages: 7061
Registered: December 2001
Senior Member
Select to_char(floor(months_between(sysdate,'8-JUN-1997')/12)) years
, floor(mod(months_between(sysdate,'8-JUN-1997'),12)) months
, floor(sysdate-add_months('8-JUN-1997',floor(months_between(sysdate,'8-JUN-1997')))) days
from dual
/

Perhaps, this is what you're looking for...

Let me know if it works (didn't have time to test thouroughly)

MHE
Re: How to calculate year, month & days between to date's [message #1878 is a reply to message #1862] Thu, 06 June 2002 22:41 Go to previous message
Chand
Messages: 3
Registered: June 2002
Junior Member
thank you so much.

it is working fine.

chand
Previous Topic: Difference between Synonym and View
Next Topic: Date Format Conversion
Goto Forum:
  


Current Time: Tue Nov 13 11:52:34 CST 2018