How to convert string(format YYYYMMDDHHMMSSSS) into the timestamp(date) value using either PQL?

TO_DATE ( table.column, FORMAT ( YYYYMMDDHHMMSSSS ) )

Abhishek,

Sorry let me rephrase my question.

I need to convert a string YYYYMMDDHHMMSSSS to a date format yyyy-mm-dd hh-mm-ss.

I tried this but is throwing a syntax error.

TO_DATE ( '20220910092700', FORMAT ( '%Y%m%d%H%M%S' ) )

Hey Anand,

Try using this:

TO_TIMESTAMP ( table.column, 'YYYYMMDDHHMISS' )

I hope this helps!

The following should be working in PQL

TO_DATE ( '20220910092700', FORMAT ( '%Y%m%d%H%M%S' ) )

Can you share your error as well ?

Change format in the indicated line to %Y-%m-%d %H:%M:%S.

Hi Balázs,

We should avoid using TO_TIMESTAMP in PQL as the same is being deprecated

No luck yet will keep trying

This is the error

Execution error: The format function requires 2 to 3 arguments.

INSERT INTO _CEL_MTP_ACTIVITIES

(

      _CASE_KEY

    , _ACTIVITY_EN

    , _SORTING

)

SELECT

    "TTR_log_file_for_Aug_2022_Christy_Analysis_xlsx_Edit"."RUN-DATE" AS _CASE_KEY

    ,"TTR_log_file_for_Aug_2022_Christy_Analysis_xlsx_Edit"."ACTIVITY" AS _ACTIVITY_EN

    , TO_DATE ( "TTR_log_file_for_Aug_2022_Christy_Analysis_xlsx_Edit"."TIMESTAMP" , FORMAT ( '%Y-%m-%d %H:%M:%S' ) )

    ,10 AS _SORTING

FROM "TTR_log_file_for_Aug_2022_Christy_Analysis_xlsx_Edit"

;

FYI,

"TTR_log_file_for_Aug_2022_Christy_Analysis_xlsx_Edit"."TIMESTAMP = 2022080114354799

Hi

I think there was a confusion since your mentioned PQL in your question, try the following

CAST(LEFT('20220910092700',8) as DATE)  || ' ' || CAST (RIGHT('20220910092700',6) as TIME)

Still getting an error Execution error: Incorrect syntax near '|'.Execution error: Incorrect syntax near '|'..

Is this query working for you?

Yes this is working for me, can you share your query again ?