condition that no of days between allotment_date-present date will be > 91 days in oracle sql

i have three tables in which one table have allotment_date.in this we have to make a condition that no of days between allotment_date-present date will be > 91 days.please help

Answer

If you want to ensure that allotment dates cannot be more than 91 days in the past (a bit of a guess – is that what you are asking?) then you would need a trigger:

create table demo
( allotment_date date );

create or replace trigger demo_allotment_date_90_day_trg
    before insert or update of allotment_date
    on demo
    for each row
begin
    if :new.allotment_date < sysdate -91 then
        raise_application_error(-20000, 'Allotment date must be within last 91 days');
    end if;
end;

This is allowed:

insert into demo(allotment_date) values (sysdate);

This will fail:

insert into demo(allotment_date) values (date '1900-12-25');

ORA-20000: Allotment date must be within last 91 days
ORA-06512: at "WILLIAM.DEMO_ALLOTMENT_DATE_90_DAY_TRG", line 3
ORA-04088: error during execution of trigger 'WILLIAM.DEMO_ALLOTMENT_DATE_90_DAY_TRG'