Date & Time Format

KNIME Analytics Platform
1

Hello

I am trying to convert a string to date & time but I can not get the correct formatting.

The contents of the first two rows are:

8/4/22 12:00 PM
10/14/21 7:00 AM

I am using a “MM/dd/yy hh:mm a” date format but I keep getting “could not be parsed” errors. Where am I going wrong?

2

Try a single M for the month part. MM would have to read as 08 instead of 8 for August.

1 Like
3

Using “M/dd/yy hh:mm a” I get:

ERROR String to Date&Time 4:3 Could not parse date in cell [Row1, column “Appointment From DTTM(Date&Time)”, row 1]: Text ‘8/4/22 12:00 PM’ could not be parsed at index 2.
RowID | … | Schedule DTTM | Appointment… | Appointment…

Row1 | … | 8/4/22 12:0… | 8/4/22 12:0… | 8/4/22 6:00 PM
^^^^^^^^^^^^^^^
Could not parse date in cell [Row1, column “Appointment From DTTM(Date&Time)”, row 1]: Text ‘8/4/22 12:00 PM’ could not be parsed at index 2.

4

Same for day M/d/yy. Looks like day lacks the preceding zero to fit the format as well.

1 Like
5

I now get this error message:

ERROR String to Date&Time 4:3 Could not parse date in cell [Row1, column “Appointment From DTTM(Date&Time)”, row 1]: Text ‘8/4/22 12:00 PM’ could not be parsed at index 13.
RowID | … | Schedule DTTM | Appointment… | Appointment…

Row1 | … | 8/4/22 12:0… | 8/4/22 12:0… | 8/4/22 6:00 PM
^^^^^^^^^^^^^^^
Could not parse date in cell [Row1, column “Appointment From DTTM(Date&Time)”, row 1]: Text ‘8/4/22 12:00 PM’ could not be parsed at index 13.

6

I have also tried “M/d/yy h:mm a” to cater for the 7:00 in row two but still get error message.

7

I am not in front of KNIME, but there is only an “a” at the end and in your format there are 2 letters. I believe that you will need “am” at the end of the format.

8

Unfortunately, I still get a “could not parsed error message”.

9

Can you upload the contents of the date column?

10

There are over 50,000 rows. Would the first 20 suffice?

8/4/22 12:00 PM
10/14/21 7:00 AM
11/30/21 7:00 AM
7/18/22 7:00 AM
12/14/21 7:00 AM
5/20/22 12:00 PM
5/25/22 12:00 PM
12/8/21 12:00 PM
10/15/21 9:00 AM
2/19/22 10:00 AM
10/5/21 7:00 AM
4/26/22 6:00 AM
9/17/21 7:00 AM
5/25/22 7:00 AM
8/17/22 7:00 AM
9/1/21 8:00 AM
4/5/22 11:00 AM
6/20/22 7:00 AM
6/14/22 7:00 AM
5/4/22 8:00 AM

11

Sample Workflow.knwf (2.5 MB)

2 Likes
12

Thanks @iCFO

So, there must have been some white spaces in the data that was causing the format issue?

13

I thought it might have been an issue initially, but you can actually pull it out and the conversion will still work. I think it was just that you needed a H for the hour instead of lowercase.

14

I am still getting parse error message. I have added string manipulation to strip any white spaces (just in case) and I have formatted as in your sample workflow but it still errors. I have even tried to change the locale from GB to US (so it matches your sample workflow exactly) but no joy.

I have re-created your sample workflow and don’t get any issues so I am not sure why it will not work when using my original data set?

image

image
image892×987 43.8 KB

image
image793×778 24.5 KB

15

Perhaps there is a format change at some point? If you can find it then you can use a row splitter to convert them separately and then concatenate them back into the full table.

17

@iCFO

All solved. I had to change the locale to en-US and change the “H” to a “h” so it would work with 12 hour clock.

Thanks for the help!!

1 Like
18

That makes sense, since in the sample you sent to me. I don’t think there was a p.m. value besides 12:00. Good catch!

1 Like
19

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