How to convert a string to a datetime format with milli seconds?

I would like to convert a string to a datetime format with the following formula:

TO_DATE('2018-01-03 00:00:00.', FORMAT('%Y-%m-%d %H:%M:%S'))

However, this is limited to accepting seconds as the finest gradient of formating according to the documentation. Is anyone aware of a %... Identifier for milliseconds?

A workaround that works for me is:

ADD_MILLIS ( TO_DATE('2018-01-03 00:00:00.', FORMAT('%Y-%m-%d %H:%M:%S')), 100)

Though being a lot less elegant and I would have to split a column into its milliseconds and subsequently join them again. A native solution would be the preferred option.

Kind Regards,

Maurits

Page 1 / 1

Hi Maurits,

As far as I know, this doesn't exists in Celonis. You can maybe simplify the formula by using LEFT and RIGHT, to take the first and last characters respectively.

ADD_MILLIS ( {t LEFT("Table1"."Column1" , 18)}, RIGHT("Table1"."Column1" , 3))

Hey Jan,

Yeah that's what I thought. Unfortunate.

Thanks for the cleaner code!

Hello,

I use this mask to get milliseconds:

TO_TIMESTAMP(BUIN_TI_START_INTERAC, 'YYYY-MM-DD HH:MI:SS.MS')

Greetings,

Carlos

Hey Carlos,

Thanks, that mask works.

Though it's a depreciated function, so Celonis should integrate Milliseconds in their to date formatting. I'll make sure to post a feature request.

Kind Regards,

Maurits

Hey Carlos,

Thanks, that mask works.

Though it's a depreciated function, so Celonis should integrate Milliseconds in their to date formatting. I'll make sure to post a feature request.

Kind Regards,

Maurits

Hey @1460069522 did this ever come to anything? Can Celonis handle miliseconds?

@gerald.dunn unfortunately, never heard back. The depreciated function should still work I think.

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN