How to fix broken dates

In some cases, you can have invalid data in your database- after importing data, or an error in SQL migrations.
For example, we have Patient resource with incorrect birthDate in the database. If we will try to search Patient by date, we will catch an error:
get
/fhir
/Patient?birthDate=2020-02-01
To fix this error we should find all incorrect dates in table via SQL query
select max((resource#>>'{birthDate}')::date) from patient;
In response we catch an error 
ERROR: date/time field value out of range: "2020-02-30"
Now we can fix these dates:
update patient
set resource = resource || '{"birthDate": "2020-02-29"}'
where resource#>>'{birthDate}' = '2020-02-30';
​
​

Last modified 11mo ago