I'm trying to create a table writing in SQL and I need to validate the 'Date of Birth' field

'Players must be over 16 years of age'

So how should I validate this. my table name is : Players
field name is DOB and the datatype is 'DATE'

I tried this 'ALTER TABLE Players SET CHECK DOB >= (16 * 365.25) ;
ERROR "Players must be above 16 years of age."

But it doesn't work. Please help :)

Member Avatar for LastMitch

I'm trying to create a table writing in SQL and I need to validate the 'Date of Birth' field

Players must be over 16 years of age

You can try this:

ALTER TABLE Players SET CHECK DOB >= DATE + (16 * 365.25);

Not tested.

Why would a person's date of birth be greater than or equal to a number?
either try

DOB >= dateadd(y,-16,getdate()) 

or

datediff(y,DOB,getdate()) >=16

Thanks for both of your replies, however none above work.
I've written
1) ALTER TABLE Players SET CHECK
datediff(y,DOB,getdate()) >=16; >>>>>DONT WORK

2) ALTER TABLE Players SET CHECK DOB >= dateadd(y,-16,getdate());
And the error i get is 'missing UNUSED keyword'

I wanted to know, could this be the problem, I've writen the values for Date of Birth as
'14-JUN-1991' >>>Is that why its not working. Instead, should I write it as '14/06/1991'

However if I write it as '14/06/1991' on SQL, that wont work (Just tested it)
and it gives me an error "not a valid month"
NOTE: The DOB field datatype is 'DATE' not 'NUMBER'
Please give your suggestions. Thank you again

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.