I am fairly new to using oracle SQL. I am trying to use the TRUNC function to calculate age
TRUNC((TO_DATE('2022-12-31')-cohort3.BIRTH_DATE)/365.25) AS AGE
but I am getting the following error –
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 40 Column: 29
The birth_date column in the cohort3 table is of type VARCHAR and formatted as YYYY-MM-DD
I tried changing VARCHAR to date but that still gave errors.
>Solution :
Don’t stored dates as strings; use a DATE data type.
Assuming that cohort3.BIRTH_DATE has a DATE data type then you want to use MONTHS_BETWEEN to calculate the age (as no year has exactly 365.25 days in it so you will introduce errors around birthdays if you use that value but all years have exactly 12 months):
FLOOR(MONTHS_BETWEEN(SYSDATE, cohort3.BIRTH_DATE)/12) AS age
If BIRTH_DATE is a string (and you are not changing it) then use TO_DATE to convert it to a DATE:
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(cohort3.BIRTH_DATE, 'YYYY-MM-DD'))/12) AS age
If you want to get a person’s age on a specific day then replace SYSDATE with a DATE literal, such as DATE '2022-12-31'.