Compare Two Dates In Different Tables 1

[Michael42]

Hello,

In a proprietary interface that can only use ANSI SQL that connects to an Oracle 9i database I need to:

1. Get and store a date value (last_updated) from one table.
2. Get and store a date value (completed_date) from a second table.
3. Determine the difference.

In PL/SQL I would normally set the values to variables and then perform a comparison on the variables but I am constrained to use standard ANSI SQL (I am not sure if or how you can use variables in ANSI SQL).

What can you suggest, can you please give an example?

Thanks,

Michael42

 

[dnoeth]

Hi Michael42,
just use two Derived Tables (Oracle calls it Inline Views) to retrieve the dates:

select [your calculation involving date1, date2]
from
(select date1 from tab1 ....) dt1,
(select date2 from tab2 ....) dt2

Of course this assumes that there's only one date from each table. If it's more then you'll have to join the Derived Tables...

Dieter

 

[LKBrwnDBA]

Unfortunately there is no ANSI SQL date-calculation solution. Every database flavor has it's own date functions which allow computations and/or date manipulation.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[dnoeth]

Of course there's Standard SQL for date/time calculation, e.g.
(date1 - date2) day

Even Oracle supports a part of it (although Oracle's date is a timestamp), IIRC this should be valid:
(date1 - date2) day to second

Dieter

 

[LKBrwnDBA]

Ooops, true, should have posted the only 'universal' one:

date1 +/- {date2 | days }



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Michael42]

Guys,

Brilliant, that worked great. Inline views are great stuff!


Thanks for posting,

Michael42