Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Question Unanswered: Need help with getting age of patient from db

    I have created a patient table in oracle as follows:

    CREATE TABLE PATIENT(
    SSN CHAR(9),
    FirstName VARCHAR(15),
    MiddleName VARCHAR(10),
    LastName VARCHAR(15),
    Address VARCHAR(20),
    DateofBirth DATE,
    PCP_Lic_No VARCHAR(6),
    PCP_Lic_State CHAR(2),
    PRIMARY KEY(SSN),
    FOREIGN KEY(PCP_Lic_No) REFERENCES DOCTOR(DOCTOR_Lic_No));

    Hi,

    I have few records in patient table and one of the patient's DOB = '03-OCT-38' so now I need to create view of all the patients who are age over 60. I do that by getting the system date and subtracting it from patient dateofbirth and dividing it by 365 then comparing it with 60 as follows:

    CREATE VIEW SENIOR_PATIENT
    AS SELECT * FROM PATIENT
    WHERE (((SELECT SYSDATE FROM SYS.DUAL) - PATIENT.DATEOFBIRTH)/365) > 60;

    So if everything works fine then this should have a record of a patient with DOB = '03-OCT-38'. But there is no record in the view. Can someone tell me what am i doing wrong or missing. I appreciate your help.
    Thanks,
    raf35

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Take out the select - sysdate gets recognized as a reserved word, and does not need a table reference. So it would be more like:
    Code:
    WHERE (trunc(SYSDATE) - PATIENT.DATEOFBIRTH/365) > 60;
    You could also use the months between function (multiply 12* months for years)

    Code:
    WHERE months_between (patient.dateofbirth, trunc(sysdate)) > 720
    Both should give you equivalent answers
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Feb 2005
    Posts
    3

    Unhappy

    I still have the same problem. I don't know if it has to do anything with the format of DOB. In the table the DOB is in the following format:

    DATEOFBIR
    ---------
    01-JAN-80
    05-FEB-81
    11-DEC-80
    03-OCT-78
    04-MAR-84
    03-OCT-38
    01-JAN-40
    05-FEB-41
    11-DEC-40
    04-MAR-44

    so clearly I have some patients who are over age 60. However, if I query the table as follows:

    select dateofbirth from patient where months_between(patient.dateofbirth, trunc(sysdate))>720;

    The result is:

    no rows selected

    And even if i use this

    CREATE VIEW SENIOR_PATIENT
    AS SELECT * FROM PATIENT
    WHERE ((trunc(SYSDATE) - PATIENT.DATEOFBIRTH)/365) > 60;

    have the same result. no rows selected. I don't know what is wrong.

    Also if I do this to get all the values, I get the following

    select dateofbirth, sysdate, months_between(trunc(sysdate), dateofbirth) from sys.dual, patient;

    DATEOFBIR SYSDATE MONTHS_BETWEEN(TRUNC(SYSDATE),DATEOFBIRTH)
    --------- --------- ------------------------------------------
    01-JAN-80 27-FEB-05 301.83871
    05-FEB-81 27-FEB-05 288.709677
    11-DEC-80 27-FEB-05 290.516129
    03-OCT-78 27-FEB-05 316.774194
    04-MAR-84 27-FEB-05 251.741935
    03-OCT-38 27-FEB-05 -403.22581
    01-JAN-40 27-FEB-05 -418.16129
    05-FEB-41 27-FEB-05 -431.29032
    11-DEC-40 27-FEB-05 -429.48387
    04-MAR-44 27-FEB-05 -468.25806


    I also don't know why I get those negative values. Any help is appreciated.
    Thanks,
    raf35
    Last edited by raf35; 02-27-05 at 14:10.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When you accept defaults you don't always get what you expect.

    Prior to rerunning the SQL below

    >select dateofbirth, sysdate, months_between(trunc(sysdate), dateofbirth) from sys.dual, patient;

    issue the following SQL

    alter session set nls_date_format='YYYY-MON-DD:HH24:MI.SS';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2005
    Posts
    3
    I know what the problem is but i don't know how to get around it.

    It is just that the date in my table is inserted as 'yy-mon-dd' and what the system does anything below 1950 (which will be entered as 50-mon-dd) converts it into 2050 or something like that and then when i do the calculation i get negative values. so now the question is how i can change those without reinserting the data into the table. I need to tell the system to treat anything below 50 the same as other dates not to convert it to something unexpected like 2050. So how can I do that. Any help is appreciated.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect you actually have two different, but related problems.
    1) Are the dates within the DB now actually have values like DOB = Jan 2038? If so, you 'll to to UPDATE so that you subtract 100 from the year.
    2) How do you get any new entries to have the correct value? (By changing the default NLS mask might work.)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Need to see the real values for those days...

    Code:
    select to_char(dateofbirth, 'DD-MON-RRRR HH24:MI:SS') from patient;
    Also, when you include sysdate in the select, don't join sys.dual. There's no need to do so.

    For new inserts, just insert the date like so:

    Code:
    insert into patient(dateofbirth) values (to_date('07/25/1920', 'MM/DD/RRRR'));
    For the existing ones, subtract 100 years from the dates that are in there. Do this:

    Code:
    update patient
    set dateofbirth = dateofbirth - 36500
    where dateofbirth >= sysdate;
    This will set the date of birth to the date of birth minus 365 days * 100 years where the date of birth is greater than or equal to today.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by SteveKaram
    Need to see the real values for those days...

    Code:
    select to_char(dateofbirth, 'DD-MON-RRRR HH24:MI:SS') from patient;
    Also, when you include sysdate in the select, don't join sys.dual. There's no need to do so.

    For new inserts, just insert the date like so:

    Code:
    insert into patient(dateofbirth) values (to_date('07/25/1920', 'MM/DD/RRRR'));
    For the existing ones, subtract 100 years from the dates that are in there. Do this:

    Code:
    update patient
    set dateofbirth = dateofbirth - 36500
    where dateofbirth >= sysdate;
    This will set the date of birth to the date of birth minus 365 days * 100 years where the date of birth is greater than or equal to today.
    I disagree with your code. It will return the wrong birthdate, yours doesn't handle leap years. Use

    update patient
    set dateofbirth = add_months(dateofbirth,-1200)
    where dateofbirth >= sysdate;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Bah, leap years. Thank you very much beilstwh, I didn't even think about those. Pesky things.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your welcome and I am glad to see a senior instructor from Oracle corp in the forum. Always nice to get the information from the horses mouth, so to speak.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Hah! Though I have to remind you that I write this stuff as myself, not as a representative of Oracle.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •