string to date MMM dd, yyyy hh:mm[:ss[.sss]] a format not working

[dlema]

Hi:

I have a table with dates as text as in the following examples:

Nov 21, 2021 08:12:31 AM

I am trying to convert to datetime with String to Date&Time node providing the following format: ‘MMM dd, yyyy hh:mm[:ss[.sss]] a’ (without the colons) but it does not work. I tried capitalizing, uppercasing, and lowercasing the month and am/pm in all possible combinations, but none of them works.

Of course I can do it with a small java script, but my user’s community are not programmers and I need a non-coding solution.

Any idea on what’s happening?

[Matt_D]

Can not explain why but setting the locale to en-US and using MMM dd, yyyy hh:mm[:ss[.SSS]] a works.

[ipazin]

Hello @dlema and @Matt_D,

this is related to locale change in Java. See here for more:

String to Date&Time worked on previous knime version but having issues with current version (4.4.1)

Hi there, I had used the string to Date&Time conversion on the previous Knime version and it had worked without issue. However it is not working on the new version - please see configuration → String is set as date-month-year hour:minutes AM/PM [image] However I get the below error message now and I can’t figure what’s wrong with the setup “ERROR String to Date&Time 4:18:265 Execute failed: Failed to parse date in row 'Row0: Text ‘16/09/2021 6:07 PM’ could not be parsed at index 16” W…

Br,
Ivan

[dlema]

The issue is related to the combination of format and language. ‘MMM’ (three letter month names) and ‘a’ (AM/PM) works in English and other languages, but not in Spanish… It seems that the problem is that these date representations are not linguistically valid in Spanish (but people and legacy applications uses it anyway). Even in java java.time.format.DateTimeFormatter ‘MMM dd, yyyy hh:mm[:ss[.SSS]] a’ fails when locale is es_XX (where XX is any of the spanish speeking countries).

At the end, I solved the problem using a java snippet node, (explained in Conversión de Fechas Arbitrarias en Knime - YouTube), but that solution is not end-user friendly ← In fact, is easier to replace Spanish three letter month names into English and use string to date&time in English, that way, non-programmers can solve the issue.