Re: Hair-Puller - Date constraint

DoctorZolo_at_gmail.com wrote:
> It sounded like such a simple little thing. Create a table of people
> where one of the attributes is date of birth (p_dob). Requirements are
> to place a constraint on the field such that data must fall between Jan
> 1, 1900 and today. Easy, right?
>
> Like this:
>
> CREATE TABLE people
> (...
> p_dob DATE CONSTRAINT people_p_dob_cc CHECK
> ((TO_CHAR(p_dob, 'YYYY-MM-DD') > '1900-01-01') AND
> (TO_CHAR(p_dob, 'YYYY-MM-DD') < TO_CHAR(sysdate, 'YYYY-MM-DD'))),
> ...);
>
> But, sqlplus has a problem using SYSDATE as an argument within a
> constraint. So, how else would anyone do something like this at the
> database layer - placing a dynamic value within a constraint clause?

Why don't you just leave in the greater than logic and ditch everything else?

p_dob DATE CONSTRAINT people_p_dob_cc CHECK (TO_CHAR(p_dob, 'YYYY-MM-DD') > '1900-01-01')), Received on Thu Jan 26 2006 - 11:30:17 CST