Wouldn't it be more accurate to do something like:
select sysdate
, &&dt b_dt,
Case
when extract(month from to_date(&&dt)) >= extract(month from
sysdate)
then extract(year from sysdate) - extract(year from to_date
(&&dt)) - 1
Else
extract(year from sysdate) - extract(year from to_date
(&&dt))
end Age
from dual
which will produce the correct year. I tried the function:
CREATE OR REPLACE FUNCTION
CAA9097.y_age(dt_1 IN date
, dt_2 in date default sysdate
) RETURN NUMBER is
BEGIN
Return
IF (extract(month from dt_1) <= extract(month from dt_2)) then
extract(year from dt_2) - extract(year from dt_1);
else
extract(year from dt_2) - extract(year from dt_1) + 1;
END IF;
END;
/
But it didn't like the IF statement. However, once my clumsyness is
fixed it seems that the function could be enhanced to show the number
of days as well. Just a thought
tim
On Jan 6, 9:11 am, Scott <[email protected]> wrote:
> Need to create a script to calculate following and display in a unique
> format (Oracle 10g, PL/SQL):
>
> (Date 1) - (Date 2)
> Example: (1/31/2009) - (2/1/2004) which would equal 1826 days
>
> I need to convert the 1826 days value to the following format:
>
> 5 years, 0 days
>
> Any ideas on how to do this?
>
> thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
