I want to load my timestamp column in Snowflake

Question

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

Marcel · Accepted Answer · 2021-11-08 07:25:40Z

2

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

answered Nov 8, 2021 at 7:25

Marcel

2,7001 gold badge8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Coder1990 Over a year ago

Is there a way to convert my timestamp into the snowflake one through azure data factory transformations or in snowflake itself ?

Marcel Over a year ago

I am not the ADF expert, but this (sqlshack.com/data-flow-transformations-in-azure-data-factory) sounds helpful. With a Data flow and a new derived column you can add expression and try to transform the column values accordingly.

Coder1990 Over a year ago

Since it's taking a very long time to do the transformations in ADF. I am loading the data as string and then will make changes in snowflake. However, when I tried to do the conversion as you suggested above, it is giving me error. I am doing the conversion on just sample data similar to my source. I am editing my original question to give all necessary information. Please take a look

Marcel Over a year ago

what is your error?

Coder1990 Over a year ago

Can't parse '09-10-2021 18:20:55' as date with format 'dd-MM-yyyy HH:mm:ss'

NiharikaMoola · Accepted Answer · 2021-11-10 12:19:19Z

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.

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.

Collectives™ on Stack Overflow

I want to load my timestamp column in Snowflake

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related