Need help with getting age of patient from db

[raf35]

I have created a patient table 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));


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

[NigelQ]

You might also be able to use something like this:

CREATE VIEW SENIOR_PATIENT
AS SELECT * FROM PATIENT
WHERE PATIENT.DATEOFBIRTH > (GetDate() - (365.25 * 60));

The above should work in SqlServer. Substitute GetDate for Now in Access/Jet.

You didn't mention which DB you're using, so hopefully you can understand the above.

Hope this helps,

- Nigel

[Krzemo]

NigelQ:
The above should work in SqlServer. Substitute GetDate for Now in Access/Jet.

It is an Oracle database (I think :rolleyes: ) - not SQL Server or Access...

raf35:
WHERE (((SELECT SYSDATE FROM SYS.DUAL) - PATIENT.DATEOFBIRTH)/365) > 60;U have potentially 2 problems:

1) some years has different number of days (366).
2) After substraction it is a numerical value which can be rounded (by converting to integer)

So maybe it will be safer to substract years (for example SELECT add_months(SYSDATE,-12*60) FROM DUAL) from SYSDATE and compare resulting value to PATIENT.DATEOFBIRTH

Best regards,
Krzemo.

[Madhi]

Try this(Sql server 200)

CREATE VIEW SENIOR_PATIENT
AS SELECT * FROM PATIENT
WHERE ((SELECT DATEDIFF(day, datefield, getdate())/365.0 FROM temptable)>60)

[Krzemo]

Madhi:

Like I said before: it is NOT SQL Server. SYSDATE function and DUAL table indicates that the RDBMS is Oracle.

Best regards,
Krzemo.