Source data is often provided in text files, with date, time, or numeric data being provided as text. A common process is to CAST (either using the function or the "::" operator) the column to the datatype that should be stored in the database destination table.
With data coming in this way, some of the rows could have an empty string for the column being CAST. Empty strings are different than NULL. It is possible to CAST a NULL to a datatype but CASTing an empty string results in an error.
The way to handle empty strings in a column is to wrap the column in a NULLIF function and convert the empty string to a NULL, which can then successfully be casted to the desired datatype.
EXAMPLE
WITH tmp AS (SELECT '2022-01-01' AS dtUNION ALLSELECT '' AS dtUNION ALLSELECT NULL AS dt)SELECT NULLIF(dt,'')::date AS castedFROM tmp