OMOP Oracle Date issues

[kirchoffkg]

The date ranges are not formatted right for oracle. ex), cond.condition_start_date BETWEEN '01-01-2021' AND '12-31-2021' for Oracle. Something like TO_DATE('21-12-31', 'RR-MM-DD')

dth.death_date and per.birth_datetime are in date format so don’t need to cast. CAST doesn’t work.
AND (EXTRACT(YEAR FROM CAST(dth.death_date AS DATE)) - EXTRACT(YEAR FROM CAST(per.birth_datetime AS DATE))) > 120
 replace with: and extract(year from dth.death_date) - extract(year from per.birth_datetime) > 120

If you hard code ’12-31-2022’ to extract the year, can you simply subtract from 2022?
WHERE (EXTRACT(YEAR FROM CAST('12-31-2022' AS DATE)) - EXTRACT(YEAR FROM CAST(birth_datetime AS DATE))) > 12
 replace with: where 2022 - extract(year from birth_datetime) > 120
 or if you want to use the date format, then
replace with: where extract(year from to_date('2022-12-31', 'YYYY-MM-DD')) - extract(year from birth_datetime) > 120

[rtmill]

Pushed a commit above. Please let us know if that resolves the issue