Snowflake: Timestamp '"2013-08-19 09:50:37.000"' is not recognized-CodePudding

in snowflake i am trying to do the following command:

copy into trips from @citibike_trips
file_format=CSV;

Before this command, i have already created a table:

CREATE TABLE "CITIBIKE"."PUBLIC"."TRIPS" 
    ("TRIPDURATION" INTEGER
      , "STARTTIME" TIMESTAMP
      , "STOPTIME" TIMESTAMP
      , "START_STATION_ID" INTEGER
      , "START_STATION_NAME" STRING
      , "START_STATION_LATITUDE" FLOAT
      , "START_STATION_LONGITUDE" FLOAT
      , "END_STATION_ID" INTEGER
      , "END_STATION_NAME" STRING
      , "END_STATION_LATTITUDE" FLOAT
      , "END_STATION_LONGITUDE" FLOAT
      , "BIKEID" INTEGER
      , "MEMBERSHIP_TYPE" STRING
      , "USERTYPE" STRING
      , "BIRTH_YEAR" INTEGER
      , "GENDER" INTEGER);

Now by typing the command copy into trips from @citibike_trips file_format=CSV;

i am receiving the error: Timestamp '"2013-08-19 09:50:37.000"' is not recognized

I have already tried the solution, which i found on the internet, which would be:

ALTER citibike SET  TIMESTAMP_INPUT_FORMAT  = 'yyyy/mm/dd HH24:MI:SS';

But this is not helping. It is also not helping, if i try to use 'yyyy/mm/dd' or 'AUTO'

Does anyone have any idea how to solve this?

CodePudding user response：

Look at the error message very closely, especially the highlighted parts:

Timestamp '"2013-08-19 09:50:37.000"' is not recognized

Your timestamp format YYYY-MM-DD HH24:MI:SS.FF3 is correct, but the string includes double quotes. You can specify FIELD_OPTIONALLY_ENCLOSED_BY = '\042' in your file format to correct this.

CodePudding user response：

Cause: Write the background information or the root cause of the problem This is because when Snowflake reads timestamp input data, it will check against a session parameter setting TIMESTAMP_INPUT_FORMAT to make sure the format passed is valid. By default, the value for TIMESTAMP_INPUT_FORMAT is AUTO, but the user can change it at session level as shown below: ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; If the format passed to timestamp function does not match with the format defined in TIMESTAMP_INPUT_FORMAT then the query will fail with above mentioned error.

Solution: We need to simply set TIMESTAMP_INPUT_FORMAT to the expected format, in this case is 'YYYY-MM-DD', or to its default value of AUTO,

ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD';

SELECT TO_TIMESTAMP('2020-09-28');

ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'AUTO';

SELECT TO_TIMESTAMP('2020-09-28');

SELECT TO_TIMESTAMP('2020-09-28 13:13:13');

I hope it helps you.