Error "invalid input syntax for type date: ""; Error while executing the query"
Published: 23 May 2013 Last Modified Date: 31 Aug 2018
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
Replace empty strings, aka blank non-NULL values, with NULL values in the database
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
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.
Thank you for providing your feedback on the effectiveness of the article.