Thread: How do I create a check constraint that is based on age difference?

Hi all,

How do I create a 'check' constraint on a date column, so that it will only
accept dates of at least 28 years ago from the date of entry?  For example,
only allowing dates of birth of people who are older than 28.

i.e.

create table foo (name varchar(40), dob date check(date('dob') < date('now' -
28 years)));

This doesn't work.  I don't understand how to do addition, subtraction, etc
with date fields.

Regards and best wishes,

Justin Clift
Database Administrator

Hi all,

I found the answer to this myself after much frustration.  The manual pages
aren't much help in this regard unfortunately.

create table foo (name varchar(40), dob date check(date_ge(date 'now' -i
nterval '28 years', dob)));

Using an 'interval' time type works perfectly.

Regards and best wishes,

Justin Clift
Database Administrator

On Fri,  5 Jan 2001 16:37, Justin wrote:
> Hi all,
>
> How do I create a 'check' constraint on a date column, so that it will only
> accept dates of at least 28 years ago from the date of entry?  For example,
> only allowing dates of birth of people who are older than 28.
>
> i.e.
>
> create table foo (name varchar(40), dob date check(date('dob') < date('now'
> - 28 years)));
>
> This doesn't work.  I don't understand how to do addition, subtraction, etc
> with date fields.
>
> Regards and best wishes,
>
> Justin Clift
> Database Administrator