I want to load my timestamp column in Snowflake-CodePudding

I have this type of data in my timestamp field in source files

11-06-2021 22:59:55

I want to load this into snowflake using ADF. In Snowflake, the timestamp format is like this '2020-03-12 01:02:03' which is different from my source data. Can you tell me what datatype I'll have to use in snowflake ? Or any other solution for this issue.

EDIT:

Table ddl :

create or replace table trial1 ( name varchar(100), t varchar(100), t1 varchar(100) );

Query :

INSERT INTO trial1 VALUES ('ABCD', '1000', '09-10-2021 18:20:55')

SELECT TO_VARCHAR(TO_DATE(t1,'dd-MM-yyyy HH:mm:ss'),'YYYY-MM-DD HH24:MI:SS.FF') from trial1

CodePudding user response：

I understand you want to change the format of your timestamp from YYYY-MM-DD to DD-MM-YYYY.

This can be achieved by calling a nested TO_VARCHAR(TO_DATE()). Here you can find a description of the idea:

Instead, use to_timestamp format to get the correct results as below.

SELECT distinct t1, 
  to_timestamp(replace(t1,'-','/'), 'dd/mm/yyyy HH24:MI:SS') to_timestamp, 
  TO_VARCHAR(to_timestamp(replace(t1,'-','/'), 'dd/mm/yyyy HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS.FF') to_varchar
  from trial1

Refer to this document for conversion functions.