compare date

[qqq]

Hi,
My question is : how to compare some date with sysdate and if the difference more than 10 days, delete those records.
I've create the trigger and following script supposed to do this but it does not.

if (sysdate - ld.trans_date_time)<10 then
RAISE_APPLICATION_ERROR(-20000,'Cannot Delete a Transaction');

It aloows delete all records, probably that all records date is dd.mm.1995.
any ideas will be appresiated.

 

[Ziyad]

Compare the date dates using some format masks.
ie to_char(sysdate) - to_charold.trans_date_time)

 

[djmartin]

Use MONTHS_BETWEEN.

eg
select months_between(date1,date2)
into diff
from dual;

If mod(diff) < 10/31 /* 31 days in a standard month */
then /* less than 10 days */
RAISE_APPLICATION_ERROR(-20000,'Cannot Delete a Transaction');