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

In article <jg0ahusbfmtftmpacndfjqcicra9r2fkc3_at_4ax.com>, whyme_at_nowhere.com says...
>
>The date is 1982...
>

not according to the database! use YYYY and 1982 and your troubles will be over. ALWAYS use 4 digits for all dates, no shortcuts.

>
>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.
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp