String to date error

Execute failed: Failed to parse date in row 'Row0: Text ‘4/1/2015 0:00’ could not be parsed at index 0

hello guys, I want to convert string to date&time. My date is written as 4/1/2015 0:00, i wrote my format under configuration as dd/MM/yyyy H:mm A. However everytime I execute it I receive the error statement written above. Help would be appreciated :blush:!

Hi @theworld, welcome to the forum.

Is there a reason why you chose to use dd/MM/yyyy H:mm A as the date format when configuring this node?
It seems to me that d/M/yyyy H:mm would work just fine.

2 Likes

Hi @theworld and welcome to the forum.

Can you try again with the format d/M/yyyy H:mm? You can use the single d or M character in the format string to represent both single and double length days and months, respectively.

Also, make sure to choose just “Date&time” for the new format, not “Date&time with zone”.

3 Likes

Hi @theworld , as @elsamuel and @ScottF have said, based on your data, the format d/M/yyyy H:mm should work for 4/1/2015 0:00 for example.

The format that you use dd/MM/yyyy H:mm means 2 digits for Day, and 2 digits for Month, so basically this data would satisfy this format: 04/01/2015 0:00

So, in your case, you need d/M/yyyy H:mm as the guys pointed out.

Also, you can try to click on “Guess data type and format” where Knime will try to guess the type and format for you (works 99% of the time, depending if your data is consistent).

And finally, the main issue here is that you are choosing Date&time with zone, but your date records do not have timezone included, which is what the message is complaining out. Choose “Date&time” instead.

This is what a Date&time with zone record looks like:
2021-05-05T13:41:02-04:00[America/New_York]

1 Like

So i did what you guys asked me to and the same error appeared. But now the error said it could not parse index Am I missing a node in my workflow? Is that why the same error keeps appearing? @bruno29a @ScottF @elsamuel

I have tried the “guess data type and format” but it keeps appearing “no suitable format found”

Hi @theworld , can you please share your data? If you can’t share the whole data because of sensitive data, just share the timestamp column only.

i have solved it already. Apparently there was a space after the H:mm

1 Like

Alright, glad it got resolved, thanks for confirming @theworld