Date format in csv can not be recognize

[MarekV]

I received a data export in CSV.
The date time structure is a bit strange.
I just need a date and time I don’t care.
The format here is
04JUN21: 11: 17: 16

I chose the first 7 characters of 04JUN21 to convert them via string to date & time to date.
Unfortunately, NODE can’t recognize it. Can you explain that to me?

Knime forum data format.knwf (42.9 KB)

I enclose the workflow.

[bruno29a]

Hi @MarekV , the MMM format recognizes the capitalized version of what you have. So Jun instead of JUN, and Nov instead of NOV.

Just convert the month to Capitalized via the capitalize() function in the String Manipulation.

Here’s further details:

String to date format: 21MAR2025

Hi @versatilka and welcome to the Knime Community. The format MMM for month supports the format of Jan, Feb, Mar, etc for month, which is basically the capitalized version of what you have (MAR vs Mar). So you can just capitalize the month via the String Manipulation like this: join(substr($column1$, 0, 2), capitalize(substr($column1$, 2, 3)), substr($column1$, 5)) This should give you 21Mar2025 You can now use the String to Date&Time node to transform this into a date type using the Date fo…

[elsamuel]

First, look at the node error message:

Ok, now we check the message in the missing cells:

Index 2 is where NOV starts, so that’s a hint as to what’s going wrong.

Your inputted Date format is ddMMMyy, however, the node description says:

MMM matches short month names like “Jan”

The String to Date&Time node is quite strict about case; you cannot use MMM to try to match an all uppercase short month name.

The solution is to either

1. convert the month names into the appropriate case using a string manipulation node and the code
   join(substr($crdateD$,0,2),capitalize(substr($crdateD$,2,3)),substr($crdateD$,5,2))
   or
2. use the String to Date/Time (legacy) node, which is less strict about case.

[MarekV]

Hello , thank you very much .
I am little bit confused but now I will some how follow your recommendation .
I have raised ticket because I had problem with date but in Italian format .https://forum.knime.com/t/problem-with-string-to-datetime-node-country-specific/41102
Recommendation was name of month has to be lower case “DIC” to “dic” not to “Dic”. I think this is country specific recognition . I used Case Converter and was ok. See workflow in my previous ticket .
For case above its is different approach
But thank you.

[Thyme]

The root cause for that confusion is how Java parses the strings, it’s not a KNIME issue. KNIME AP version 4.4 onwards uses a different Java version. Maybe you can try to use the legacy mode mentioned here:

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

BUT… in spite of my previous reply you are right @Sajith !! I was surprised about the case of the AM/PM, as I thought it should be upper case, so I just went and tried it in KNIME 4.3.4 Using the format mask dd/MM/yyyy h:mm a KNIME 4.3.4, converts 16/09/2021 6:07 PM KNIME 4.4.1 converts 16/09/2021 6:07 pm They both complain about the opposite case. (I was testing this using locale en-AU as per the screenshot. The result of other locales may vary I guess) Likewise the “a” in the “Date & T…

No promises though, as that thread is about capitalistion of AM/PM in different locales.

A bit more info:

Error Parsing String with String to Date&Time

Hi @christiam, I assume this problem occured with the String to Date&Time Node? You’re right, from how I understand the format specifiers this should indeed work (but it doesn’t). However, I was able to get the node to work by changing the locale from de-AT to en-US. I hope that gets you back online until someone from the team can comment on this. Happy New Year, T String to Date&Time – Node Documentation DateTimeFormatter (Java Platform SE 8 ) (oracle.com) [parse_error]