On Jan 7, 2:49 pm, "Michael Moore" <[email protected]> wrote:
> assuming you don't care about leap years, use the MOD function.
>
>
>
> On Tue, Jan 6, 2009 at 7: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- Hide quoted text -
>
> - Show quoted text -
Is this what you were thinking:
SQL> with years as (
2 select
3 trunc(((to_date('&&highdt','MM/DD/RRRR') - to_date('&&lowdt','MM/
DD/RRRR')) - mod(to_date('&&highdt','MM/DD/RRRR') - to_date
('&&lowdt','MM/DD/RRRR'), 365))/365) years
4 from dual
5 ),
6 days as (
7 select
8 mod(to_date('&&highdt','MM/DD/RRRR') - to_date('&&lowdt','MM/DD/
RRRR'), 365) days
9 from dual
10 )
11 select years, days
12 from years, days;
Enter value for highdt: 01/31/2009
Enter value for lowdt: 02/01/2004
old 3: trunc(((to_date('&&highdt','MM/DD/RRRR') - to_date
('&&lowdt','MM/DD/RRRR')) - mod(to_date('&&highdt','MM/DD/RRRR') -
to_date('&&lowdt','MM/DD/RRRR'), 365))/365) years
new 3: trunc(((to_date('01/31/2009','MM/DD/RRRR') - to_date
('02/01/2004','MM/DD/RRRR')) - mod(to_date('01/31/2009','MM/DD/RRRR')
- to_date('02/01/2004','MM/DD/RRRR'), 365))/365) years
old 8: mod(to_date('&&highdt','MM/DD/RRRR') - to_date('&&lowdt','MM/
DD/RRRR'), 365) days
new 8: mod(to_date('01/31/2009','MM/DD/RRRR') - to_date
('02/01/2004','MM/DD/RRRR'), 365) days
YEARS DAYS
---------- ----------
5 1
SQL>
This syntax is valid for 9i and later releases of Oracle.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
