Error "invalid input syntax for type date: ""; Error while executing the query"

KNOWLEDGE BASE

Error "invalid input syntax for type date: ""; Error while executing the query"

Published: 23 May 2013
Last Modified Date: 31 Aug 2018

Issue

When using a calculated field, the following error might occur:

Database error 0x80004005: ERROR: invalid input syntax for type date: "";
Error while executing the query

Environment

Tableau Desktop
PostgresSQL
Greenplum

Resolution

Option 1

Replace empty strings, aka blank non-NULL values, with NULL values in the database

Option 2

Use a calculated field to convert empty strings to NULL. For example, use:
```
IF [date field]="" THEN NULL ELSE [date field]) END
```
Use the calculated field from above in place of [date field]. For example, inside of DATE() or converting to the date data type

Cause

This is a database limitation where some databases, cannot cast blank values, aka empty strings, as dates. This may occur when using the DATE() function or when converting a string field to the date data type.