Formatting String to Date Time

[Page0727]

I have a string 29-OCT-21 06.08.44.093000000 AM. How would I format this using the string to Date time node? I have tried everything without success.

Thanks

[bruno29a]

Hi @Page0727 the format that you have is not a valid format.

Basically the issue is with the Month value. There is a format “MMM” that comes close, so with some string manipulation, you should be good. Essentially, MMM is the Capitalized version of the first 3 letters of the month name.

For example: Jan, Feb, Mar, etc, and in your case, it would be Oct.

You can do some manipulation and modify the OCT to Oct by doing a:
capitalize(lowerCase())

EDIT: Actually you don’t even need the lowerCase(). capitalize() will do it for you. So, just use the capitalize() functon.

[Page0727]

Is the AM going to cause an issue? Does it need to be removed?

Thanks

[bruno29a]

Hi @Page0727 , you can use the mask “a” for AM/PM

Just make sure your locale is set to en-US, or the MMM will not work. Well, the mask “a” will also not work I think, so you have to set your locale to en-US

After doing your manipulation, you should end up with:
29-Oct-21 06.08.44.093000000 AM

This mask should work for that format:
dd-MMM-yy hh.mm[.ss[.SSS]] a

[Page0727]

I had to use capitalize($$CURRENTCOLUMN$$,“-”) on the date. However I am still not getting it to work using dd-MMM-yy hh.mm[.ss[.SSS]] a. Thanks for your help.

[bruno29a]

I’m not sure what the “-” is for in your function, but you should not capitalize the whole string, it will also capitalize the AM/PM.

You will not get the mask dd-MMM-yy hh.mm[.ss[.SSS]] a working if you don’t see “29-Oct-21 06.08.44.093000000 AM”

You can try:
join(capitalize(substr($$CURRENTCOLUMN$$, 0, 6)), substr($$CURRENTCOLUMN$$, 6))

[Page0727]

@Page0727

29-OCT-21 06.08.44.093000000 AM

I used “-” because it seems to only affect the AM/PM. I think it is looking for a preceding space. as the output is

29-oct-21 06.08.44.093000000 AM

Thanks

[bruno29a]

Ok, try this then:
join(substr($$CURRENTCOLUMN$$, 0, 3), capitalize(substr($$CURRENTCOLUMN$$, 3, 3)), substr($$CURRENTCOLUMN$$, 6))

[Page0727]

@bruno29a

dd-MMM-yy hh.mm[.ss[.SSS]] a

This worked. Thank you so much.

[takbb]

Oh my favourite subject… The decision by the creators of the Java date/time classes to make datetime case sensitive using MIXED case (and worse to be arbitrarily differently case sensitive in different locales) has to surely rate among the worst design decisions ever made… (And don’t get me started on handling of languages with accents (diacritics) on some letters!)

It causes no end of complication to what should be an otherwise simple task… What were they thinking…? Or what have I missed?