Importing Date into Snowflake tables-CodePudding

I have a CSV file that I am trying to load into Snowflake manually. I have created the tables and have the column type as DATETIME for the columns with dates.

The data is in the CSV is 20210709. This is getting imported into SNOWFLAKE as 1970-08-22

I cannot determine why that is.

I have another file with the date as 2/18/2022 and that is getting imported correctly.

Any help is appreciated

Sridhar

CodePudding user response：

You should define the DATE_FORMAT parameter in the FILE_FORMAT clause or when creating the FILE FORMAT object.

Example:

copy into mytable
  file_format = (type = 'CSV', DATE_FORMAT = 'YYYYMMDD')
  pattern='.*/.*/.*[.]csv[.]gz';

CodePudding user response：

The short answer is: YYYYMMDD is not a standard recognized date format, so you'll need to specify the format when parsing it.

Michael's answer is a great way to approach this, and I wanted to provide an alternative:

Here is some example syntax for specifying parsing and "transformations" per column during a COPY INTO import. Documentation

You'll need to have a stage established which can itself have parameters for file_format. Documentation

copy into mytable (date_col, second_column)
from (
    select TO_TIMESTAMP($1, 'YYYYMMDD'), $2
    from @mystage/filename.csv
)