Extract and convert Datetime field from JSON in SNOWFALKE-CodePudding

I am receiving a Datetime field in my JSON which looks like the below screenshot where first 4 characters are year, next two characters are month, next two characters are date, next two characters are hour, next two characters are minutes and last two characters are seconds.

I extracted the date successfully and tried to convert using to_timestamp function in SNOWFLAKE but returns me the result like "2606-08-31 03:17:04.416"

code that I am working with to convert into Timestamp

select distinct to_timestamp(a.value) within group(order by to_timestamp(a.value)) from orders a

Could someone please help me with this?

CodePudding user response：

The default format for conversion is AUTO, which will attempt to guess. It appears as though it is guessing incorrectly.

To specify a format, add a second parameter to your call to to_timestamp that is a string of the format. Here are the docs.

to_timestamp(a.value, 'YYYYMMDDHH24MISS')