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 Wed, 05 June 2002 21:16
 Chand Messages: 3Registered: 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.

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
 Maaher Messages: 7061Registered: 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
 Maaher Messages: 7061Registered: 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
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
 Chand Messages: 3Registered: 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