Having trouble converting String to Datetime

[arjunt1217]

The format of the string is as such:

04-FEB-23 12.30.00.000000000 AM
15-FEB-23 09.15.00.123456789 PM
12-JAN-23 06.32.42.400000000 AM

I would like to convert this to a datetime format while keeping the accuracy with the 9 trailing digits after the seconds.

This is the formula i tried

DateTimeParse([PLANNED_START_DATE], '%d-%b-%y %I.%M.%S.%9 %p')

%d = 2 digit date

%b = 3 letter month

%y = 2 digit year

%I = 2 digit hour on a 12 hr clock

%M = 2 digit minutes

%S = 2 digit seconds

%9 = 9 trailing digits after the seconds?

%p = AM/PM

The output is always null.

My current workaround is getting rid of the extra digits then using this formula which works successfully. 

DateTimeParse([PLANNED_START_DATE], '%d-%b-%y %I.%M.%S %p')

I would like to avoid the workaround and find a solution to keep the extra digits. 

Here is a sample workflow showing both my failed attempt and the workaround. 

[DataNath · Accepted solution]

Hey @arjunt1217, if you just up the size in the Data type configuration, that'll allow the whole value to parse out without being truncated:

[arjunt1217]

So i am unable to change the size of the data type in the formula tool. It is greyed out. Any ideas why?

[DataNath]

Hey @arjunt1217, what version of designer are you using? The functionality for additional sub-second precision was added in 2023.1 so if you're on a lower version it won't be available: https://help.alteryx.com/release-notes/en/release-notes/designer-release-notes/designer-2023-1-relea...

[Qiu]

@arjunt1217 
I can not change the size of the field also even i am with the latest version.

Maybe @DataNath is using a different version of alteryx?

A workaround is that we will first make it string then use select tool to change it to DateTime with 29 size.

[DataNath]

Good call @Qiu! I'm on 2023.1 myself 🙂