ERROR invalid input syntax for type date SQL state: 22007

previous post - Install TeamPostgreSQL on Ubuntu 14

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

Related posts :
List all duplicate Foreign Key constraint in PostgreSQL

List the all the FOREIGN KEY constraints with create/drop statement.