Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Hair-Puller - Date constraint
hpuxrac wrote:
>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')),
>
>
>
Presumably because we don't want to record a date of birth until after
the baby actually emerges into the world!
Received on Thu Jan 26 2006 - 11:39:35 CST