Date '2017/02/23' not recognized in SnowFlake

Question

I have a csv with example data as:

61| MXN| Mexican Peso| 2017/02/23

I'm trying to insert this into snowflake using the following commands:

create or replace stage table_stage file_format = (TYPE=CSV,ENCODING = 'WINDOWS1252');

copy into table from @table_stage/table.csv.gz file_format = (TYPE=CSV FIELD_DELIMITER='|' error_on_column_count_mismatch=false, ENCODING = 'WINDOWS1252');

put file://table.csv @table_stage auto_compress=true

But I get the error as

Date '2017/02/23' not recognized

Using alter session set date_input_format = 'YYYY-DD-MM' to change the date format fixes it.

But what can I add in the create stage or the copy command itself to change the date format?

Answer 1

Snowflake has session parameter DATE_INPUT_FORMAT that control over the input format for DATE data type.

The default value is AUTO specifies that Snowflake attempts to automatically detect the format of dates stored in the system during the session meaning the COPY INTO <table> command attempts to match all date strings in the staged data files with one of the formats listed in Supported Formats for AUTO Detection.

To guarantee correct loading of data, Snowflake strongly recommends explicitly setting the file format options for data loading (as explain in documentation)

To solve your issue you need to set the DATE_INPUT_FORMAT parameter with the expected format of dates in your staged files.

Answer 2

Just set the date format in the file format: https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html

Answer 3

Use the DATE_FORMAT parameter in file_format condition. More you can read here: COPY INTO

copy into table 
from @table_stage/table.csv.gz 
file_format = (TYPE=CSV
               FIELD_DELIMITER='|'
               ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
               ENCODING = 'WINDOWS1252'
               DATE_FORMAT = 'YYYY/MM/DD');