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

Thanks,
I understand it now.

On Sun, 23 Jun 2002 00:38:05 +0200, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>Ok,
>
>I will spell it out for you
>
> INSERT into STUDENT
> VALUES
> (100, 'Stalioraitis', 'Tamara', 'S', '144 Windridge Blvd.',
> 'Stillwater', 'NY', '12170', '5184371802', 'SR',
>TO_DATE('07/14/82','MM/DD/RR'), 8891, 1);
>
>Before you do this, just select the original record with
>to_char(student_dob,'MM/DD/YYYY') and you will see you got 2082
>in your data.
>
>Regards
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address
>
>
>On Sat, 22 Jun 2002 21:06:16 GMT, whyme_at_nowhere.com wrote:
>
>>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 - 18:24:53 CDT