how to know if between 2 date's it past 5 days - Oracle 10g ?

Question

I have two date's:

1) 01/05/2009
2) 06/05/2009

How can I know if there's 5 days between them?

Answer 1

First, get your dates into variables. Then you can use simple addition to determine if the required number of days have passed as Oracle treats addition and subtraction with dates as adding or subtracting days.

For instance Date1, + 5 will equal the date plus 5 days.

In PL/SQL your block may end up looking something like this:

declare
  date1 date := to_date('01/05/2009', 'DD/MM/YYYY');
  date2 date := to_date('06/05/2009', 'DD/MM/YYYY');
begin
  if date1 + 5 >= date2 then
    dbms_output.putline('Date 2 is more than five or more days past date 1!');
  else
    dbms_output.putline('There is less than five days between date 1 and date 2!');
  end if;
end;

Answer 2

You can subtract two dates to get the difference in days between them (the unit for date columns in Oracle is 1 day).

In your example, I assume date is spelled in DD/MM/YYYY format. So you could do this:

select case when
    abs(to_date('01/05/2009','DD/MM/YYYY') - to_date('06/05/2009','DD/MM/YYYY')) = 5
  then 'YES'
  else 'NO'
  end as ARE_DATES_5_DAYS_APART
from
  dual;

If the two dates are two columns in a table, then use table name instead of "dual" in query above.

Answer 3

First decide whether your date format is DD/MM/YYYY or MM/DD/YYYY