How can i check that a user is 12 and over sql

How do I check that when a user enters their date of birth into my database that they are 12 and over?

I am using SQL plus.

Right now I have somthing like this:

create table test(dateofbirth date not null,
 CONSTRAINT dateofbirth_ck CHECK(TO_CHAR(dateofbirth, 'YYYY-MM-DD') <= '2002-02-09')
);

The problem with this is that it does not update every day, so the ‘2002-02-09’ should be changed every day.

I know what to do but I’m not sure how to execute it. I want to get the ‘Dateofbirth’ & sysdate – 12 years and if user is above 12 it will let them.

Answer

Oracle won’t you let use sysdate in a check constraint (or other functions whose return value is not constant over time).

You can, however, write a trigger that implements the same logic:

create or replace trigger trg_mytable_dateofbirth
before insert or update on mytable
for each row
begin
    if :new.dateofbirth > add_months(sysdate, -12 * 12)
    then
        raise_application_error( -20001, 
            'invalid date of birth - current value:'
            || to_char(:new.dateofbirth, 'yyyy-mm-dd')
            || ', limit as of today:'
            || to_char(add_months(sysdate, -12 * 12), 'yyyy-mm-dd')
        );
    end if;
end;
/

Demo on DB Fiddle:

insert into mytable(dateofbirth) values (date'2000-01-01')
-- 1 rows affected

insert into mytable(dateofbirth) values (date'2010-01-01')
-- ORA-20001: invalid date of birth - current value:2010-01-01, limit as of today:2008-02-10
-- ORA-06512: at "FIDDLE_MYQIVTFXTMKGROKMNOGB.TRG_MYTABLE_DATEOFBIRTH", line 4
-- ORA-04088: error during execution of trigger 'FIDDLE_MYQIVTFXTMKGROKMNOGB.TRG_MYTABLE_DATEOFBIRTH'