Unable to cast string to date when inserting on Databricks SQL

Question

I have a table created like so :

CREATE TABLE IF NOT EXISTS MyDataBase.Table (
  `date`     DATE,
  `name`     STRING,
  `isTarget` BOOLEAN
) USING DELTA LOCATION '/mnt/path/to/folder/'

I need to manually insert values in the table using SQL. I tried to do it like so :

INSERT INTO CalendrierBancaire.Data VALUES (
  ('2022-01-01', 'New Year', True)
)

But it fails on this error :

Error in SQL statement: AnalysisException: cannot resolve 'CAST(`col1` AS DATE)' due to data type mismatch: cannot cast struct<col1:string,col2:string,col3:boolean> to date; line 1 pos 0;

I also tried to replace the date string with :

CAST('2022-01-01' AS DATE)
to_date('2022-01-01', 'yyyy-MM-dd')

But neither worked and returned the same error. It looks like the SQL parser wants to convert the whole row to date which is stupid. Do you have any idea how I can do it ?

Thanks.

PS : In real usage I have almost 30 to 40 lines to insert so letting a variable with the CAST expression while be painful to realize for all values.

score 0 · Answer 1 · answered Feb 04 '22 at 10:39

It looks like the problem is that you have additional brackets around values that you want to insert, so it's interpreted as a single column - you need to use following syntax (see docs):

INSERT INTO CalendrierBancaire.Data VALUES 
  ('2022-01-01', 'New Year', True)

or if you have multiple rows to insert, then list them via comma:

INSERT INTO CalendrierBancaire.Data VALUES 
  ('2022-01-01', 'New Year', True),
  ('2022-02-01', 'New Year 2', True)

P.S. Although it works for me just fine even with extra brackets

Unable to cast string to date when inserting on Databricks SQL

1 Answers1