How to convert string to date time value in snowflake

user15488631 0
Viewed 39

I am new to Snowflake and need help in converting string to date time.

I am loading the .csv file into Snowflake stage table which is all having string columns. One of the column name END_TIME_STAMP (STRING) having values like below

END_TIME_STAMP
08-09-2022 06:23:10 AM
08-09-2022 11:10:10 PM

My requirement is how to convert this values to 24 hr time format in snowflake.

OutPut:

END_TIME_STAMP
08-09-2022 06:23:10.000
08-09-2022 23:10:10.000

i tried using TO_TIMESTAMP(), TO_TIMESTAMP_NTZ(), but it throughs me below error Timestamp '6:23:20 AM' is not recognized

snowflake-cloud-data-platform
1 Answers

Using TRY_TO_TIMESTAMP with time format:

The AM format specifier allows values with either AM or PM.

ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF3';

SELECT TRY_TO_TIMESTAMP('08-09-2022 06:23:10 AM','DD-MM-YYYY HH:MI:SS AM')
      ,TRY_TO_TIMESTAMP('08-09-2022 06:23:10 PM','DD-MM-YYYY HH:MI:SS AM');

Output:

enter image description here

Lukasz Szozda0
Related