OraFAQ Forum: SQL & PL/SQL » How to calculate year, month & days between to date's

Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate year, month & days between to date's

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

How to calculate year, month & days between to date's [message #1862]

Wed, 05 June 2002 21:16

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

Report message to a moderator

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: 7065
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

Report message to a moderator

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: 7065
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

Report message to a moderator

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: 3
Registered: June 2002

Junior Member

thank you so much.

it is working fine.

chand

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 01:51:19 CDT 2024