Convert String to Date in Snowflake

Question

I have been wrapping my head around with the weird behavior I am getting the below output when I am trying to run this query.

SELECT
 to_timestamp_tz('11082013 12:29:25', 'MMDDYYYY hh24:mm:ss') A
,to_timestamp_tz('11082013 12:29:25', 'MMDDYYYY hh:mm:ss') B
,to_timestamp_ntz('11082013 12:29:25', 'MMDDYYYY hh24:mm:ss') C
,to_timestamp_ntz('11082013 12:29:25', 'MMDDYYYY hh:mm:ss') D
,to_date('11082013 12:29:25', 'mmddyy hh:mm:ss') E

I am expecting the output to be 2013-8-01 for the date part in all the cases.

Not sure what I am missing here. Did research on the Snowflake documentation but wasn't able to get any concrete findings. Any help is appreciated.

Answer 1

You need to represent MINUTES using mi instead of mm. E.g.

to_timestamp_tz('11082013 12:29:25', 'MMDDYYYY hh24:mi:ss')

mm represents months, so in the original it is actually adding 29 months instead of 29 minutes to the date basis.