search

ncats / CTSA-Metrics

17 stars 16 forks source link

OMOP Oracle Date issues #48

Open kirchoffkg opened 2 years ago

kirchoffkg commented 2 years ago

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 commented 2 years ago

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