Home » SQL & PL/SQL » SQL & PL/SQL » check date
check date [message #287797] Thu, 13 December 2007 09:02 Go to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Hello all!

I wonder if anybody can help me. I have a table with two columns. One column stores a number to represent 'year' and the other stores a date.

How do i compare the year column (using check constraint) with the year part of the date column.

Many Thanks for your time,

Bashair
Re: check date [message #287798 is a reply to message #287797] Thu, 13 December 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sysdate, extract(year from sysdate) year from dual;
SYSDATE                   YEAR
------------------- ----------
13/12/2007 16:04:54       2007

Regards
Michel
Re: check date [message #287810 is a reply to message #287798] Thu, 13 December 2007 10:59 Go to previous messageGo to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Thanks Michel!!

Does this work with create statement? for example i used


SQL> create table Course(
Course_ID number(2),
Student_Name varchar2(15),
Course_Start_Date Date,
student_Year_Of_Birth number(4),
Constraint Check_Years check (Course_Start_Date > extract (year from student_Year_Of_Birth))
);

its the line highlighted in bold is what I am concerned about the most.
I must admit that this table is not a very good example but that's the best thing I can think about now Embarassed .


Any help is appreciated Smile
Re: check date [message #287811 is a reply to message #287810] Thu, 13 December 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you get when you pressed "Enter" after your statement?

You extract year from a date not from a number.

Regards
Michel
Re: check date [message #287820 is a reply to message #287811] Thu, 13 December 2007 11:46 Go to previous messageGo to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Oh sorry, I made a mistake, may be this is a better example

SQL>Constraint Check_Years check (Year_Of_Graduation > extract (year from Date_Of_Birth))

This is just an example of the problem I am trying to solve.
The message I am getting is "Missing right parenthesis".

To be honest, I think its Oracle SQL Developer is not functioning properly because it kept crashing. I will check it later and will post a reply.

Thanks for your help Smile
Re: check date [message #287828 is a reply to message #287797] Thu, 13 December 2007 12:52 Go to previous messageGo to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Yeah it works now, something was wrong with Oracle Developer I think.

Thanks for your help Smile

Bashair
Re: check date [message #288181 is a reply to message #287828] Sat, 15 December 2007 08:29 Go to previous messageGo to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member

Hi,
Can the check constraint be used on columns outside the table I am creating, e.g.


student table
---------------
student_ID
DOB

course table
---------------
course_ID
start date

student_course table
---------------
course_ID
student_ID

In student_course table is it possible to check that student DOB is before the course start date without adding DOB and start date columns?

Thanks,

Bashair
Re: check date [message #288183 is a reply to message #288181] Sat, 15 December 2007 09:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CHECK constraint can not refer to columns in other tables.

However, you may create a database trigger which would take care about such a requirement. Here's an example:
CREATE OR REPLACE TRIGGER trg_dob_course
  BEFORE INSERT ON STUDENT_COURSE
  FOR EACH ROW
DECLARE
  l_dob STUDENT.dob%TYPE;
  l_start_date COURSE.start_Date%TYPE;
BEGIN
  SELECT dob INTO l_dob
    FROM STUDENT
    WHERE student_id = :NEW.student_Id;
				
  SELECT start_date INTO l_start_date
    FROM COURSE
    WHERE course_id = :NEW.course_id;
				
  IF l_start_date < l_dob
  THEN
     RAISE_APPLICATION_ERROR(-20100, 'Student DOB < course start date');
  END IF;
END;							
I didn't pay attention to possible NO-DATA-FOUNDs (if a student or course do not exist).
Re: check date [message #288251 is a reply to message #288183] Sun, 16 December 2007 08:07 Go to previous message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Thank you!!
Previous Topic: Fetch out of sequence error ....
Next Topic: ORA-27046 when transfer files
Goto Forum:
  


Current Time: Sat Dec 10 10:41:18 CST 2016

Total time taken to generate the page: 0.07620 seconds