DATE datatype

I am developing a student advising system, and I need to enter the date field as Semester, Year. Eg Fall 1992, Spring 1992. etc.

I am not to sure how to implement this but one of the ways I have thought of is:

Have a table with the values of the semesters

CREATE TABLE SEMESTERS
(SEM CHARACTER(7));
INSERT INTO SEMESTERS
        VALUES('FALL');
INSERT INTO SEMESTERS
        VALUES('SPRING');
INSERT INTO SEMESTERS
        VALUES('SUMMER'); I also have a student info table with the values

CREATE TABLE STUDENT
(SSN NUMBER(9),

	SEM CHARACTER(7),
	YR NUMBER(2)
        COMMDATE DATE);

I would then run the student form and when it came to the sem field I would run the LIST_VALUES procedure and list the values of the SEMESTER table as the options that could be selected. So student.sem would have the values of either fall, spring, or summer. The user could then enter the proper year in the yr field.

I could then have a procedural statement which would check the sem value and assign a value to a variable mon depending on the value of the sem. eg if sem = fall, mon = 8 etc.

SO I have the month in the mon variable and year in the yr field. I dont forsee any problem in any of the above. The problem is how do I transfer the two values to COMMDATE.

I know you can do

   UPDATE STUDENT
      set commdate = TO_DATE('92 08','YY MM');

but how would I do that if the values are stored in the variables yr and mon.

Any help in the above matter would be highly appreciated.

As I am new to oracle I am not sure I have gone about the problem in the right way, any suggestions to tackle the problem in any other way too, would be welcome.

My email address is vkg1_at_ra.msstate.edu

Vinay

--

Vinay Gidwani