convert two date formats of string type to one type

KNIME Analytics Platform
1

Hello KNIME Users,

Greetings to all!

I was searching through the forum but did not find any solution to below:
I am trying to convert string to date&time data type. I know of the node “String to Date&time” but that node only accepts one format.
But, I have 2 formats of date one column as below:

image

here I want to extract these 6 columns as date&time from string data type.

I configured “String to date&time” node to extract using following configurations:

image
image930×899 99 KB

Here it does work for the one I parsed but throws error on 00:00 as below:
ERROR String to Date&Time 0:7 Execute failed: Failed to parse date in row '1: Text ‘00:00’ could not be parsed at index 0

Kindly help me understand how I can parse multiple date formats from same column from string to date and time data type using KNIME?

Thanks in advance!

2

Hello @mahima_goyal,

String to Date&Time node can not handle multiple formats. Start from bottom topic and follow linked topic for more ideas:

Br,
Ivan

3 Likes
3

Hello @ipazin!
The solution in option 1 will not help as the format change without any pattern and I need for all 5 columns, any column can have any format in any row without fixed pattern.
I am following the solution in option 2 and will see if that works and get back to you with the same.

Thanks,
Mahima.

4

Hello @ipazin !
None suggestions work for me as mentioned as I just saw my data set has even more formats…
Even regex does not seem to work.
I have some 3-5 formats in one column randomly occurring among the 5 consecutive columns.
the below are the formats:
hh:mm:ss
hh:mm
YYYY-MM-DD’T’hh:mm:ss
YYYY-MM-DD

And I want to convert these to date&time data type.
Is there any other way to handle this?

Thanks,
Mahima.

5

Hi Mahima,

In your case Regex option should work.
Can you upload your dataset here?

Best,
Mehrdad

1 Like
6

Hello @mahima_goyal,

you can explore following options:

  • write regex (or use set of nodes) that will convert all known and unknown formats to one format which will then be used in String to Date&Time column
  • split your data in KNIME based on format and then apply one String to Date&Time node for each split
  • format data prior to reading it into KNIME

Br,
Ivan

1 Like
7

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.