Snowflake copy into not recognising timestamp

user_15078512 0
Viewed 46

Any suggestions on the below, i am trying to use copy into to move parquet files from S3 into snowflake table. Col1 is a timestamp and the rest are strings.

copy into table1 from
(select $1:col1, $1:col2, $1:col3
from @stage/path)
file_format = (format_name = parquet_format);

and getting the following error

Failed to cast variant value "20050111 00:00:00" to TIMESTAMP_NTZ

I have tried

copy into table1 from
(select to_timestamp($1:col1, 'yyyymmdd hh:mi:ss'), $1:col2, $1:col3
from @stage/path)
file_format = (format_name = parquet_format);

but getting the error

Error: too many arguments for function [TO_TIMESTAMP(GET(STAGE.$1, 'col1'), 'yyyymmdd hh:mi:ss')] expected 1, got 2 (line 130)

Any ideas

snowflake-cloud-data-platform
1 Answers

What's happening is this in this expression:

(select to_timestamp($1:col1, 'yyyymmdd hh:mi:ss')

This part: $1:col1 is resolving to an object rather than a primitive data type. This is what's leading to the error message about getting 2 arguments instead of one.

Changing this part of the expression to $1:col1::string casts the expression as a primitive type - string or varchar. This is the type of parameter the to_timestamp function is expecting. The final statement should be:

copy into table1 from
(select to_timestamp($1:col1::string, 'yyyymmdd hh:mi:ss'), $1:col2, $1:col3
from @stage/path)
file_format = (format_name = parquet_format);
user_127563810
Related