Home » SQL & PL/SQL » SQL & PL/SQL » Date Validation (Oracle 10g)
Date Validation [message #389803] Tue, 03 March 2009 11:37 Go to next message
Mr_SQL
Messages: 2
Registered: March 2009
Junior Member
Hi all

I am trying to write an sql constraint which checks that the date of birth falls into the range (Sysdate - 16 years) I am fairly new to this and think I need to use a trigger but am not to sure how to implement it.
Re: Date Validation [message #389804 is a reply to message #389803] Tue, 03 March 2009 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Date Validation [message #389806 is a reply to message #389803] Tue, 03 March 2009 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried and explain why it does not fit the requirements.

Regards
Michel
Re: Date Validation [message #389808 is a reply to message #389803] Tue, 03 March 2009 11:53 Go to previous messageGo to next message
Mr_SQL
Messages: 2
Registered: March 2009
Junior Member
Data Definition for the table

Create table Users(
Name varchar(70),
DateOfBirth Date
)

-----

Adding data

Inset into users 'John', '11-May-1962'

-----

I wish to check that John is over 16 before he is commited to be added to the table

Thank You

[Updated on: Tue, 03 March 2009 11:54]

Report message to a moderator

Re: Date Validation [message #389811 is a reply to message #389803] Tue, 03 March 2009 12:01 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Mr_SQL wrote on Tue, 03 March 2009 18:37
Hi all

I am trying to write an sql constraint which checks that the date of birth falls into the range (Sysdate - 16 years) I am fairly new to this and think I need to use a trigger but am not to sure how to implement it.

I am not new to SQL, however after reading your post, I am also not able to "implement it". Have a look at your post again. It does not contain the most important part - when shall that constraint come into action?
It is not possible to assure this condition by check constraint (as SYSDATE is not deterministic, data could become "invalid" just as time goes), the only way is to use a trigger. Of course it depends, on which action it shall be implemented (INSERT, UPDATE, some regularly repeating action, ...).
After specifying this, you may easily come to a solution.
Or, even better, re-think the design and the need of having this "sql check" at all (e.g. apply this condition in queries), as data may become invalid without any action.
Re: Date Validation [message #389813 is a reply to message #389808] Tue, 03 March 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I wish to check that John is over 16 before he is commited to be added to the table

Create a trigger on insert that will check the date.

By the way, '11-May-1962' is not a date, it is a string.

Regards
Michel
Re: Date Validation [message #389836 is a reply to message #389808] Tue, 03 March 2009 14:13 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just in case you didn't understand, here are a few more words about the issue.

Saying that '11-may-1962' is a string means that you should use the TO_DATE function, such as
INSERT INTO users
  (date_of_birth) values (TO_DATE('11-may-1962', 'dd-mon-yyyy'))
Otherwise, there are just too many unknown factors that may affect your data. For example, you could have also entered "date" as '10.02.09'. This might be 'dd.mm.yy', 'mm.dd.yy', yy.dd.mm', ... and you don't want to let environment decide what it is supposed to be.

As of your problem, perhaps you might investigate use of the ADD_MONTHS function. "16 years" would, actually, be "16 x 12 months". Try to construct a logic which will use "date of birth", SYSDATE and ADD_MONTHS. Shouldn't be too difficult.
Previous Topic: How to login Sqlplus (merged)
Next Topic: Sorting and Sorting in oracle (2 threads merged by bb)
Goto Forum:
  


Current Time: Fri Dec 09 05:58:06 CST 2016

Total time taken to generate the page: 0.12808 seconds