HI,
I HAVE DATE DATA IN STRINGS FORMAT IN CSV FILE ( DATE FORMAT IS “DD-MMM-YYYY” OR “DD-MMM-YY”)
I AM NOT ABLE TO TRANSFORM DATA IN CSV READER NODE.
I WANT TO CONVERT STRING DATA IN DATE FORMAT.
HOW TO DO IT.
I TRIED “STRING TO DATE&TIME NODE” AND “STRING TO DATE/TIME (LEGACY)” BUT DINT WORKED.
EG. OF DATA
Please refrain from typing in all caps. It makes your post difficult to read.
In the future, please also give details about what exactly you did. Just saying you tried a node doesn’t really give us much information. The specifics tend to matter.
If you’d like to convert 01-APR-2021 to a KNIME date format, you’ll need to first convert the all caps abbreviated month to title case. That is, instead of “APR” it should be “Apr”. This can be done with the capitalize function in the String Manipulation node.
Then you can use the String to Date&Time node to covert the string. Use the date format dd-MMM-yyyy
Sorry I will refrain from all caps.
I didn’t know that we can manually type in dd-MMM-yyyy In string to date& Time node. I thought we have to select from the drop-down menu only.
And Thanks It worked.
And What if I have 2 types of Format in the same column.
In the dates column, I have dd-MMM-yyyy and dd-mmm-yy number of instances of the latter part is very few. (1300)
String to Date&Time node show 1300 rows could not be converted.
You’ll need to find a way separate out the rows that have the 2 different formats. One approach is to use a Row Splitter node with the regex [0-9]{2}-[a-zA-Z]{3}-[0-9]{2}. This will push all the dd-MMM-yy formatted dates to the top output, and the rest to the bottom.
Then you can operate on each branch with String to Date&Time nodes to convert the dates, then use a Concatenate node to get a single table.
Not able to filter data with Row Splitter node with the regex `[0-9]{2}-[a-zA-Z]{3}-[0-9]{2}.
@armingrudd Can you help.
Hi @ganeshpawar,
An alternative approach is given in the following workflow.
This demonstrates two flows, one which can convert pretty much any date in UK d-M-y order, irrespective of case, separators or length, and scroll down for the equivalent to convert US M-d-y format.
A string Manipulation followed by a String to Date&Time node with a very flexible format mask can, for example be used to convert all of the following dates without requiring multiple branches. The requirement for it to work is that the order of d M and y is consistent within the data.
UK Format
US Format
Kudos
Thank you 
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
