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

<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

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address