Snowflake chooses timestamp format over date format on load of CSV

Question

I have a CSV with a column containing basic dates (like 01/11/2020).

I have specified in the FILELOADER the following format specifications:

DATE_FORMAT = 'MM/DD/YYYY'
TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI'.

This should create a match for the date columns using the DATE_FORMAT. However, Snowflake sees the date column and identifies it as a timestamp column. Since no time exists in that column it fails to match the TIMESTAMP_FORMAT and the load fails.

Why is Snowflake choosing to identify my date column as a timestamp column?

Here is the full FILE FORMAT specification I am using:

CREATE OR REPLACE FILE FORMAT jobstats_csv_format
  TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null') EMPTY_FIELD_AS_NULL = true COMPRESSION = AUTO FIELD_OPTIONALLY_ENCLOSED_BY='"' DATE_FORMAT = 'MM/DD/YYYY' TIMESTAMP_FORMAT = 'MM/DD/YYYY  HH24:MI';

... this file format specification is referred to in my stage definition ...

CREATE OR REPLACE STAGE tct_jobstats_input_stage
  URL='azure://blahblahblah.blob.core.windows.net/tct-filefly-input/'
  CREDENTIALS=(AZURE_SAS_TOKEN='blahblahblahblahblah')
  FILE_FORMAT = jobstats_csv_format;

... and the code I am using to load it...

    try 
    {
        var load_cmd  = "COPY INTO LifelineRemoved ";
            load_cmd += "FROM @tct_jobstats_input_stage ";
            load_cmd += "PATTERN='.*Lifeline Removed During Date Range.*[.]csv.*';";
        var load_stmt = snowflake.createStatement( { sqlText: load_cmd } );
        load_stmt.execute();
    }
    catch (err)
    {
        result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
        result += "\n  Message: " + err.message;
        result += "\nStack Trace:\n" + err.stackTraceTxt; 
    }

... and here is a (header + single) line of data from the CSV which fails to load. account,customerstatus,certifieddate,lifelineremoveddate,lifelineremovedreason,telephonenumber1,linestatus,activationdate,disconnectdate,firstname,lastname,serviceaddress1,serviceaddress2,servicecity,servicestate,servicezip,Column1 123456,Active,11/26/2019,12/1/2019,DSN D Denied 90-5 The customer has had their LifeL,(321) 5551212,Active,11/26/2019,,JOHN,DOE,1524 EXAMPLE ST,,FRESNO,CA,93706,

... and finally the error I am seeing...

waldente waldente · Accepted Answer · 2020-01-11T14:35:10

TIMESTAMP_FORMAT is a preview feature at this time, so it may not be ready for production use. As a workaround, you can use the COPY INTO mytable FROM (SELECT FROM ... ) pattern to apply custom transformations on each field. Or load as text and convert after. Lastly it always helps if you can provide the code and sample data.

Snowflake chooses timestamp format over date format on load of CSV

3 Answers