On Thu, January 15, 2009 12:14, timmg wrote: > > 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 > > > If is a statement .. not an expression
use a Case expression and no .. i did not read the content of the problem... That is just the really obvious issue that you have. Rob --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
