I want to load my timestamp column in Snowflake

user_15674807 0
Viewed 510

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

azure-data-factory snowflake-cloud-data-platform azure datetime
2 Answers

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: How to change the Date format of a date filed in snowflake?

Basically you convert the initial timestamp to a varchar and then you convert it back with TO_DATE and pass your desired as well as the input format. Besides the Year-/Month/Day-Part, you also have to add the Hours and Seconds-format.

TO_DATE: https://docs.snowflake.com/en/sql-reference/functions/to_date.html Date and Time Formats: https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#date-and-time-formats

user_124804310

In snowflake, dashes and slashes are not interchangeable in date formats.

Slashes imply DD/MM/YYYY format, and dashes imply YYYY-MM-DD format. (Example: 2021/10/09’ or ‘10-09-2021’ are not interpreted as expected).

In your query to get the result, first, replace the dash (-) with a slash (/) and then convert to date.

When you are using To_date it accepts timestamp but results only date part.

  • When you convert to_date to get timestamp it gives all zeros in place of the timestamp as shown below.

enter image description here

  • 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

enter image description here

Refer to this document for conversion functions.

user_159691070
Related