google-bigquerysqldatatypes

Unable to alter column data type in Big Query

We imported database into BigQuery but a lot of columns are not in correct data types, many of them are stored as STRING. I want to fix them by change the data type in BigQuery

ALTER TABLE my.data_set.my_table ALTER COLUMN create_date SET DATA TYPE DATE;

But I got

ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRING) is assignable to the new type (DATE)

How to solve it?

Solution

  • Unfortunately, as far as I know there is no way to convert data type from STRING to DATE using ALTER TABLE but to create it again with the schema you want.

    CREATE OR REPLACE TABLE testset.tbl AS 
SELECT 'a' AS col1, '2022-05-16' AS create_date
 UNION ALL
SELECT 'a' AS col1, '2022-05-14' AS create_date
;

-- ALTER TABLE testset.tbl ALTER COLUMN create_date SET DATA TYPE DATE;

-- Query error: ALTER TABLE ALTER COLUMN SET DATA TYPE requires that
-- the existing column type (STRING) is assignable to the new type (DATE) at [7:25]


-- Create it again. 
CREATE OR REPLACE TABLE testset.tbl AS
SELECT * REPLACE(SAFE_CAST(create_date AS DATE) AS create_date) 
  FROM testset.tbl;