CODEWITHSUNDEEP
sqloracle-databaseoracle10gdate
Gold
Gold

Reputation: 62524

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

I have two date's:

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

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

Upvotes: 0

Views: 2185

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 1

Pop
Pop

Reputation: 4022

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.

Upvotes: 6

Sonny Boy
Sonny Boy

Reputation: 8016

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;

Upvotes: 1

Related Questions