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

  1. Use a calculated field to convert empty strings to NULL. For example, use:
    IF [date field]="" THEN NULL ELSE [date field]) END
  2. 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.
Did this article resolve the issue?