1.11M Members

SQL Validation Rules for 'Date'

 
0
 

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 :)

LastMitch
Deleted Member
 
0
 

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.

 
0
 

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
 
0
 

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

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article