Re: newb problem when trying to "getAge()"

James wrote:

> I am using Oracle 9i on windows XP.
> I am trying to create a method that produces the age from the object table.
> I have been searching on forums and know the basic code that should work but
> I am getting strange results which may be due to the format of my date.
> First of all, here are the results I get when I use the code
>
> RETURN SYSDATE - DATEOFBIRTH;
>
> S.GETAGE()
> ----------
> -14481.223
> -15858.223
>
> I know I can use trunc and make the numbers after the decimal dissapear but
> obviously the result is still not correct.
>
> Here is the code I have used to get this far:
>
> CREATE OR REPLACE TYPE student_type AS OBJECT
> (FirstName varchar2(15),
> LastName varchar2(15),
> Address address_type,
> Contact contact_type,
> DateOfBirth date,
> Nationality varchar2(15),
> AttendanceMode varchar2(15),
> StudentNo varchar2(15),
> Userid varchar(15),
> FeeStatus varchar2(25)
> MEMBER FUNCTION getName RETURN varchar2,
> PRAGMA RESTRICT_REFERENCES (getName, WNDS, WNPS, RNDS, RNPS),
> MAP MEMBER FUNCTION getLname RETURN varchar2,
> PRAGMA RESTRICT_REFERENCES (getLname, WNDS, WNPS, RNDS, RNPS),
> MEMBER FUNCTION getEmail RETURN varchar2,
> PRAGMA RESTRICT_REFERENCES (getEmail, WNDS, WNPS, RNDS, RNPS),
> MEMBER FUNCTION getAge RETURN number,
> PRAGMA RESTRICT_REFERENCES (getAge, WNDS, WNPS, RNPS)
> )
> /
>
> CREATE OR REPLACE TYPE BODY student_type IS
> MEMBER FUNCTION getName RETURN VARCHAR2 IS
> BEGIN
> RETURN (FirstName || ' ' || LastName);
> END;
>
> MAP MEMBER FUNCTION getLname RETURN VARCHAR2 IS
> BEGIN
> RETURN (LastName);
> END;
>
> Member function getEmail return varchar2 IS
> BEGIN
> Return self.contact.email;
> END;
>
> MEMBER FUNCTION getAge RETURN NUMBER IS
> BEGIN
> RETURN SYSDATE - DateOfBirth;
> END;
>
> END;
> /
>
> CREATE TABLE student_table OF student_type;
>
> INSERT INTO student_table
> VALUES ('Dean', 'Martin', address_type('56 Las Vegas Rd', 'Las Vegas', 'LV4
> T65', 'USA'), contact_type(01290120120102, 023932048393,
> 'd.martin_at_gre.ac.uk'),
> '15-JUL-43', 'USA', 'Full Time', '00023053984', '3498348734', 'Paid');
>
> INSERT INTO student_table
> VALUES ('Sammy', 'Davis', address_type('57 Las Vegas Rd', 'Las Vegas', 'LV4
> T66', 'USA'), contact_type(9875487, 4574584598, 's.davis_at_gre.ac.uk'),
> '22-APR-47', 'USA', 'Part Time', '000273847', '2387387', 'Half Paid');
>
> Any Help would be great.
>
>

SQL> select sysdate - to_date('15-JUL-43','DD-MON-YY') from dual;

SYSDATE-TO_DATE('15-JUL-43','DD-MON-YY')
----------------------------------------
                               -14481.065

SQL> select sysdate - to_date('15-JUL-1943','DD-MON-YYYY') from dual;

SYSDATE-TO_DATE('15-JUL-1943','DD-MON-YYYY')

                                   22043.9351

43 is closer to 2003 than to 1900, oracle assumes the 43 to be 2043: SQL> select sysdate - to_date('15-JUL-2043','DD-MON-YYYY') from dual;

SYSDATE-TO_DATE('15-JUL-2043','DD-MON-YYYY')

                                   -14481.064

-- 
Regards, Frank van Bortel