Re: Oracle 8i query returning a negative age

whyme_at_nowhere.com wrote in message news:<86j9hug68hf9iaretthmvckk9qak2aqjbq_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

Hi John,

There's nothing wrong with the query. However, you should try using the RR date format as the date of birth is being treated as 2082 instead of 1982 (Note the numeric value) Thus instead of 20, you are getting -80 as age value.

Regards,
Ambalika Received on Tue Jun 25 2002 - 09:52:46 CEST