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.
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.