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

The date is 1982...

On 22 Jun 2002 15:05:00 -0700, Thomas Kyte <tkyte_at_oracle.com> wrote:

>In article <f7j9hu01gl6hmpp91v90aqt360o2nsenug_at_4ax.com>, whyme_at_nowhere.com
>says...
>>
>>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
>
>sigh, I would have thought we all would have learned this very very important
>lesson 2 years ago.
>
>Tell me:
>
>TO_DATE('07/14/82','MM/DD/YY')
>
>what is that date? 1982? 2082? 1882? 2182?? 82??
>
>You need to use 4 digits '07/14/1982', 'mm/dd/yyyy'
>
>ALWAYS. Someone might say "oh, just use rr", as in "mm/dd/rr", then Oracle will
>see that as 1982. But -- if this is a real student system -- you'll have people
>born in 1949 perhaps -- and now what? they'll be going in as 2049 and you'll
>have the problem all over again (rr is just a "trick", quick fix that uses a
>sliding window to decide what the century is).
>
>USE 4 DIGITS for all years, everytime, all of the time....
>
>also, consider trunc(months_between( sysdate, dob )/12) as way to get the age.
Received on Sat Jun 22 2002 - 18:02:59 CDT