I am trying to load data from SQL server to Snowflake environment. SQL gives output of date as --06/28/2016 20:35:06:703000'

While inserting this value in snowflake with timestamp(9) as datatype, I am getting this error -- Timestamp '06/28/2016 20:35:06:703000' is not recognized

If I am inserting-- '06/28/2016 20:35:06' --> This is getting inserted.

I have tried

1. ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'AUTO';
2. ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'MM-DD-YYYY HH24:MM:SS.FF6';
3. ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'AUTO';
4. 2. ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'MM/DD/YYYY HH24:MM:SS.FF6';

Nothing worked as of now.

can someone help ?

CodePudding user response：

If I use lower case versions of your format string it works for me:

Well, if mi is used for minutes, parameters format AND the marker for the milliseconds is : not . in you example data

select  '06/28/2016 20:35:06:703000' as inp
    ,try_to_timestamp(inp, 'mm/dd/yyyy hh:mi:ss:ff6') as ts1
    ,try_to_timestamp(inp, 'mm/dd/yyyy hh24:mi:ss:ff6') as ts1b
    ,try_to_timestamp(inp, 'MM/DD/YYYY HH24:MM:SS.FF6') as ts2
    ,try_to_timestamp(inp, 'MM/DD/YYYY HH:MI:SS.FF6') as ts3
    ,try_to_timestamp(inp, 'MM/DD/YYYY HH:MI:SS.FF6') as ts4
    ;

With Session format:

ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'mm/dd/yyyy hh:mi:ss:ff6';
select '06/28/2016 20:35:06:703000'::timestamp;

seems to work for me, albeit still not actually an insert

With data insert:

ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'mm/dd/yyyy hh:mi:ss:ff6';

create table ts_test(ts timestamp_ntz);

insert into ts_test  select '06/28/2016 20:35:06:703000';

select * from ts_test;

this also works for me:

insert into ts_test values ('06/28/2016 20:35:06:703000');