How to parse varchar to actual time value?

Question

I am trying to insert the data into the final table in snowflake from the staging table. When the command is run it give the error:

Can't parse '20211101132344205550' as timestamp with format 'YYYYMMDD HH24:MI:SS.FF'

My table definition and insert statement is here.

I used the same kind of method last time it worked. Thank you so much in advance.

CREATE OR REPLACE TABLE dw.tb_fidctp_order(
sysdate DATE,
record_id NUMBER(18,0) ,
timestamp TIMESTAMP_NTZ(9),
<trim_excess>
);



INSERT INTO dw.tb_fidctp_order(
    sysdate, 
    record_id, 
    timestamp,
<trim_excess>
    )
 SELECT 
     TO_DATE(LEFT(timestamp, 8), 'YYYYMMDD')
    ,CAST(record_id AS NUMBER(18,0))
    ,TO_TIMESTAMP(LEFT(timestamp,24),'YYYYMMDD HH24:MI:SS.FF') 
<trim_excess>
 FROM stg.tb_fidctp_order_input;

Answer 1

In Snowflake you need to define what your format is. So, if all of your timestamps are formatted as a straight string like that, then you are defining it incorrectly. Try something more like this:

SELECT to_timestamp(left(EXPIRY_DATETIME,24),'YYYYMMDDHH24MISSFF');

The to_timestamp() function is defining how the input string is formatted, not how you want the timestamp to be formatted as an output.

Answer 2

So the error message is the critical point, your formating string for timestamps have space and : time formatting, which needs to be removed.

Below I have used the tru_to_timestamp function because it returns NULL istead of erroring with is helpful to show the problem is your formatting:

SELECT 
    '20211101132344205550' AS a
    ,try_to_timestamp(a, 'YYYYMMDD HH24:MI:SS.FF') as b
    ,try_to_timestamp(a, 'YYYYMMDDHH24MISSFF') as c;

gives:

A	B	C
20211101132344205550		2021-11-01 13:23:44.205

which shows your ,TO_TIMESTAMP(LEFT(timestamp,24),'YYYYMMDD HH24:MI:SS.FF') should be:

,TO_TIMESTAMP(LEFT(timestamp,24),'YYYYMMDDHH24MISSFF') 

and then you would have no problem.