create an empty string in DATE format BigQuery SQL-CodePudding

I need to create an empty string/null value. It has to be DATE type. I am UNIONing a couple of tables. Table 1 has date column, table 2 and 3 do not. Below is the snippet of code for one table that doesn't have date column.

SELECT
      CAST(TIMESTAMP('') AS DATE) AS date,
      coalesce(id, 'unknown') AS id
FROM
`my_table`

I received the following error: Invalid timestamp: '' Is there a way to create null value with DATE type? Thank you in advance.

CodePudding user response：

select cast(null as date) as date

with output