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

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 - 16:06:16 CDT