Re: Need help on an Oracle 8i query returning a negative age.....

Ok,

I will spell it out for you

 INSERT into STUDENT
 VALUES
 (100, 'Stalioraitis', 'Tamara', 'S', '144 Windridge Blvd.',  'Stillwater', 'NY', '12170', '5184371802', 'SR', TO_DATE('07/14/82','MM/DD/RR'), 8891, 1); Before you do this, just select the original record with to_char(student_dob,'MM/DD/YYYY') and you will see you got 2082 in your data.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

On Sat, 22 Jun 2002 21:06:16 GMT, whyme_at_nowhere.com wrote:

>I am not following you...Where to I insert RR?
>
>Thanks
>John
>
>
>On Sat, 22 Jun 2002 22:23:19 +0200, "Sybrand Bakker"
><postbus_at_sybrandb.demon.nl> wrote:
>
>>
>><whyme_at_nowhere.com> wrote in message
>>news:f7j9hu01gl6hmpp91v90aqt360o2nsenug_at_4ax.com...
>>> When I run this query in 8i, it is returning a negative age. Here is
>>> the table structure:
>>>
>>> CREATE TABLE student
>>> (student_id NUMBER(6),
>>> student_last VARCHAR2(30),
>>> student_first VARCHAR2(30),
>>> student_mi CHAR(1),
>>> student_add VARCHAR2(25),
>>> student_city VARCHAR2(20),
>>> student_state CHAR(2),
>>> student_zip VARCHAR2(9),
>>> student_phone VARCHAR2(10),
>>> student_class CHAR(2),
>>> student_dob DATE,
>>> student_pin NUMBER(4),
>>> faculty_id NUMBER(6),
>>> CONSTRAINT student_student_id_pk PRIMARY KEY (student_id),
>>> CONSTRAINT student_faculty_id_fk FOREIGN KEY (faculty_id) REFERENCES
>>> faculty(faculty_id));
>>>
>>> Here is one of the records that were inserted:
>>>
>>> INSERT into STUDENT
>>> VALUES
>>> (100, 'Stalioraitis', 'Tamara', 'S', '144 Windridge Blvd.',
>>> 'Stillwater', 'NY', '12170', '5184371802', 'SR', TO_DATE('07/14/82',
>>> 'MM/DD/YY'), 8891, 1);
>>>
>>>
>>> Finally, here is the query:
>>>
>>> SELECT student_first, student_last, student_id, TRUNC((SYSDATE -
>>> student_dob)/365.25)
>>> FROM student
>>> WHERE student_class = 'SR';
>>>
>>> When I run this query, it returns the infomation with a negative age:
>>>
>>> STUDENT_FIRST STUDENT_LAST
>>> STUDENT_ID
>>> ------------------------------ ------------------------------
>>> ----------
>>> TRUNC((SYSDATE-STUDENT_DOB)/365.25)
>>> -----------------------------------
>>> Tamara Stalioraitis
>>> 100
>>> -80
>>>
>>>
>>> Am I doing something wrong? I have the system date in my pc right.
>>> Thanks!
>>> John
>>
>>
>>Nothing to do with the system date
>>
>>Here it is
>>TO_DATE('07/14/82',
>>'MM/DD/YY')
>>This mask will automatically append missing elements, so it will append the
>>current century.
>>The data is stored internally as
>>07/14/2082
>>If you do not want to supply the century, you need to replace YY by RR
>>
>>Hth
Received on Sat Jun 22 2002 - 17:38:05 CDT