Messy Date & Time String Reformatting

I cannot figure out the most simple way to take this string column date & time input:

12/3/18 8:16 PM

And turn out the following local date:

2018-12-03

Please help!

I’ve tried various string manipulations that are based on characters in each cell but having a range of 12/3/18 thru 1/20/19 means I’m dealing with changes in the character amounts when month and day range between one and two digits.

Thank you…

Additionally, it would be nice to capture the time as military time in a separate column:

string column:
12/3/18 8:16 PM

local time column:

08:16

Thank you (again).

Have you tried the string to Date/Time conversion with this format which should cover AM and PM

dd/MM/yyyy hh.mm aa

I think the node can also auto guess the right format.

Also you can refer to.:

https://www.knime.com/blog/the-new-date-time-integration

I am not at my trusted machine right now so I can’t test it.

That doesn’t work. I don’t think KNIME will recognize a 12/3/18 11:44 AM string pattern in either the legacy or updated String to Date/Time node.

Can you suggest a regex string manipulation approach to separate the date and time strings and reformat with string manipulation before I try to apply the Date/Time conversion node?

Thanks

If it helps, here’s the date/time string column I’m trying to convert from the data set.

MessyDateTimeString.xlsx (67.3 KB)

Hey @t2jr0385,

I got it working with the legacy String to Date/Time and the following format:

MM/d/yy K:mm a

Have not gotten it to work with the new String to Date/Time node because your data contains hourformats like 12:00 PM. K (Hours here) can only be in the range from 0-11.

For extracting the military time I would use a Extract Date&Time Fields (use a Legacy Date&time to Date&time to convert your date&time to a usable format).
Cheers,
Johannes

1 Like

Also, DataExtractor is doing the job.

1 Like

OK I did a combination of the

  • format “MM/dd/yy HH:mm a”
  • extract the hour
  • if the hour is 12 or AM nothing is added
  • if the hour is PM 12 hours are added
  • a date time shift brings back the military/real hour

I wonder why it would not work with the original format because Java should be able to do that. Please check the solution. We Europeans use to struggle with the AM/PM concept.

kn_example_messy_date_time.knwf (370.2 KB)

1 Like