Friday, March 18, 2016

ERROR invalid input syntax for type date SQL state: 22007

ERROR invalid input syntax for type date SQL state: 22007

SQL state: 22007

Type casting varchar to date in where clause

select * from patient_history where
where admissiondate::DATE > releasedate::DATE


Error

ERROR:invalid input syntax for type date:"" ********** Error **********

ERROR:invalid input syntax for type date:""
SQL state: 22007
Solution

In my case, type casting for admissiondate working fine but not for releasedate. Then i spent few time for the field releasedate then found few empty / null values in releasedate column.

select * from patient_history where
releasedate is null OR releasedate =''

I removed empty entry from the releasedate , then the same query for type casting working fine.

select * from patient_history where
v.admissiondate::DATE > releasedate::DATE

No comments: