Problem in String to Date&Time Node

KNIME Analytics Platform
1

I have a column called “startdate” with the following content:
2017-04-10 13:20:04.9720000
2017-10-03 08:30:03.0000000
2017-01-11 09:52:24.5430000
2015-11-12 09:25:05.1200000
2017-12-15 10:48:50.8400000
2017-09-05 09:09:43.6350000
2018-02-13 12:43:16.2740000
2018-02-02 14:39:44.1610000
2017-07-18 08:36:38.7180000
2017-03-30 07:13:32.8200000
2018-02-21 08:40:22.6450000
…
…
I checked the length of all the cells and is always 27 characters, so no inconsistencies.

when I connect the node “string to date&time” I select date format yyyy-MM-dd HH:mm:ss.S
and the node fails. Error message is "date in row: Row0 2017-04-10 13:20:04.9720000 could not parsed, unparsed text found at index 21

How can I solve it?

2

Hi there!

Only one S means only one digit millisecond part :wink:

Use this format: yyyy-MM-dd HH:mm:ss.SSSSSSS

Br,
Ivan

5 Likes
3

My date time colummn has this pattern:
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00
2020-08-21 10:29:20.0087042 +02:00

I have tries these formats:
yyyy-MM-dd HH:mm:ss.SSSSSSSZ for String to Date/Time(legacy)
yyyy-MM-dd HH:mm:ss.SSSSSSSXXX for String to Date/Time

None of these nodes work. Any idea what I can try?

4

Hi @Stervis,

you got it almost, just a space is missing :slight_smile: That should work with the String to Date&Time node:
yyyy-MM-dd HH:mm:ss.SSSSSSS XXX

Best,
Simon

3 Likes
5

Worked like a charm. Thanks

3 Likes
6

Is there a doc to know what to use considering the various patterns? Thanks

7

Hi @Daniel_Weikert

You can find documentation with examples in the node description which also links to this more detailed documentation: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html
There you find examples for every letter that can be used in the pattern.

Best,
Simon

1 Like