Unable to set a datetime-like string variable as column value in Matillion

Question

I am trying to set a string variable with the following format "YYYY-MM-DD HH:MM:SS" as a column value in Matillion with an SQL script component.

My initial code was this:

UPDATE "DB_MX_DEV"."STAGING".${target_table}
SET insertion_date = '2022-09-24 19:14:10';

The code from above worked with no problem. The table column is updated with that value (note that the value is hardcoded for testing purposes). However, when I try to run the following code:

UPDATE "DB_MX_DEV"."STAGING".${target_table}
SET insertion_date = (${fecha_insercion});

I got this error message:

SQL compilation error:
syntax error line 2 at position 32 unexpected '19'.
syntax error line 2 at position 36 unexpected '28'.
syntax error line 2 at position 39 unexpected '51'.

The fecha_insercion variable had this value 2022-12-06 19:28:51. I don't understand why if I reference the variable the code does not work. I tried to set the variable type both as text and datetime types but the error persist.

Answer 1

Why are you using parens to surround the variable? If you use single quotes it works fine. Like this:

UPDATE "DB_MX_DEV"."STAGING".${target_table}
SET insertion_date = '${fecha_insercion}';