Date format conversion Jul 8, 2015, 7:04:16 PM


I’m trying to get convert dates with the date/time node, but I’m not able to get it. My data looks as following/attached.

Jul 22, 2015, 6:25:49 PM
Jul 19, 2015, 6:45:15 PM
Jul 15, 2015, 6:45:17 PM
Jul 12, 2015, 10:21:48 AM
Jul 11, 2015, 9:39:28 AM
Jul 8, 2015, 7:04:16 PM
Jul 4, 2015, 7:49:53 AM
Jun 29, 2015, 7:10:13 PM
Jun 27, 2015, 10:10:01 AM
Jun 25, 2015, 5:37:35 PM

I tried with “‘MMM d, yyyy’, ‘h:mm:ss’ a” but it’s not working. What am I missing?


1 Like

What format do you want to convert to?

Currently it’s in string format and I would like it to have in actual date time format for easy comparison with other data sets: yyyy-mm-dd hh:mm:ss (no am/pm).

Hi @robvp

Very close, this should get you going: MMM dd, yyyy, h:mm:ss a


Hi @robvp The case of AM and PM is locale dependant. What locale are you using? I think en-GB converts upper case but en-US is lower case. It might be the other way round. Try both. :wink:

See also String to Date&Time worked on previous knime version but having issues with current version (4.4.1) - #3 by takbb

I think you need a single “d” for day. I’m running 5.2 and the “dd” fails.
MMM d, yyyy, h:mm:ss a

1 Like

Can you share a screenshot of the configuration window? I’m not getting it to work :frowning: Perhaps - as mentioned below - it depends on the locale. I tried en-US en en-GB but didn’t work.
I’m always getting the following error:

Could not parse date in cell [Row0, column “Activity Date”, row 1]: Text ‘Jul 22, 2015, 6:25:49 PM’ could not be parsed at index 0.

Sure! The locale is en-US

1 Like

Hi @ArjenEX and @robvp , as per @rfeigel’s note, I found that with dd it fails on a single digit day, e.g. “Jul 8, 2015, 7:04:16 PM” so agree that the mask needs to be MMM d, yyyy, h:mm:ss a to handle all the rows in the initial post.

Also, yes I concur that it works for en-US and fails for en-GB. This is attributable to the uppercase AM/PM. If it is lower case “am/pm”, en-US would fail, and en-GB would work.

@robvp the only way I could get KNIME to produce the error message

Could not parse date in cell [Row0, column “Activity Date”, row 1]: Text ‘Jul 22, 2015, 6:25:49 PM’ could not be parsed at index 0.

from the date Jul 22, 2015, 6:25:49 PM

was to introduce an additional space at the start of the the format mask, so it was
instead of

If you still can’t get it working there is something else we’re missing about your data, or environments. Can you upload a small workflow with just your sample data and the String to date&time node ?

1 Like

Here’s a workflow that seems to work. I used the data you originally posted.

1 Like

Thanks!! This is working indeed!!

However, I checked my settings and yours. They seem identical, but somehow your node was working.

These are your working ones:

And there are my failing ones:

They’re both connected to the same source:

Hmm…when I copied your date format to mine my configuration also worked. Very strange. I don’t know what the problem is, but it’s solved now. Perhaps I used - by accident - a hidden character somehow causing it not to work.

Found it! It’s because there was a space in front of the first M…such a foolish mistake.

Hi @robvp , as I mentioned earlier the only way I could make it produce that error was if there was a space at the beginning of the format mask… Anyway glad you found it. Have a great Christmas.

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