I have two string columns where date and time values with time zones are present. Need to convert those into date and time datatype. I tried with yyyy-MM-dd HH:mm:ss.[SSS] Z using String to Date&Time node. However, still getting error. Reference table attached below - kindly help.
Col1
2008-08-01 16:45:58.8 UTC
2008-08-08 12:23:43.717 UTC
2008-08-09 22:34:02.267 UTC
2008-08-12 08:51:20.96 UTC
2008-08-14 02:39:38.667 UTC
2008-08-14 14:00:08.087 UTC
2008-08-18 14:21:43.443 UTC
2008-08-20 03:36:11.473 UTC
2008-08-27 16:49:51.413 UTC
2008-09-01 01:16:46.44 UTC
2008-09-01 14:18:27.543 UTC
2008-09-02 11:59:43.763 UTC
2008-09-11 13:36:07.73 UTC
2008-09-15 12:29:38.807 UTC
2008-09-15 12:36:09.667 UTC
2008-09-15 16:20:55.46 UTC
2008-09-15 19:04:53.367 UTC
2008-09-15 23:54:30.83 UTC
2008-09-16 06:44:41.81 UTC
2008-09-16 11:04:05.557 UTC
2008-09-16 15:54:58.837 UTC
@omprakashjena welcome to the KNIME forum. The problem seem to be the first line with only one number behind a second. I would have expected the [SSS] part to fix that but it did not.
As an additional note, you can actually mix-and-match formats into a single format mask, using a the series of optional masks (in square brackets) as per @mlauber71 's example.
This should mean just a single Date&Time node without the need to re-join and additional rules for missing values.
For example, I think that if you used the following it would match 3 digit, 2 digit or 1 digit milliseconds as appropriate, and correctly parse all of your date/times in a single hit:
yyyy-MM-dd HH:mm:ss.[SSS][SS][S] zzzz
(the important trick is that the optional masks MUST be specified in descending order of length, so as to attempt the most-selective SSS first, before failing over to attempting SS and finally S).
The downside is that if you had “bad” data that somehow matched all of the optional masks, there might be an issue, but for most purposes, data will be of a defined format (albeit with variable length milliseconds) such as in your example, and this mask should work perfectly well.
Nice one @takbb , I was actually trying to do this - mix-and-match, somehow I was not successful.
EDIT: Ahh, I see why “the important trick is that the optional masks MUST be specified in descending order of length”. That’s why it did not work for me. The first reaction, at least for me, was to do [S] first, cause that was the first case I saw.
