convert from string to date format

[Jeff]

I have a string column with a value of 2/25/2020 and need to convert it to datetime. I use the node String to DateTime and give it the format of MM/dd/YYYY but it gives me an error. What format string is to be used when the month has a single digit?

Thanks!
Jeff

[izaychik63]

On configuration press Guess date type and format. Node will chose the one that works.

[Jeff]

Hi, Thanks for response. Unfortunately that didn’t quite work…

See quick format of simple workflow.

In table creator I have 1 column string with value of “2/25/2020”.

In string to Date node I list column form as MM/dd/YYYY but it fails giving error as ERROR String to Date&Time 19:2 Execute failed: Failed to parse date in row 'Row0: Text ‘2/10/2020’ could not be parsed at index 0

I guess MM/dd/YYYY doesn’t seem to work even if I append a 0 to string. Also choosing guess data type and format doesn’t seem to work.

Did I do something wrong?

Thanks,
Jeff

[izaychik63]

Red box shows you that you format is not a date&Time but Date.

[Jeff]

I changed to date but it still gives me an error. Thoughts?

[izaychik63]

Did you press the button?

[Jeff]

yes… in red is says No suitable format found! Date format is MM/dd/YYYY and new type shows date.

still not working… I’m using knime 4.1.1

Thoughts?

[izaychik63]

Try format M/d/yyyy .

[Jeff]

Unfortunately I try M/D/YYYY and it does not work. I also try M/d/YYYY it doesn’t work either. Sigh…

Thanks for responding…

[izaychik63]

Y should be in small letters.

[Jeff]

I try M/D/yyyy with no success. M/d/yyyy works! Thank you very much!

Thank you! Might you suggest a link to see the format strings for future efforts?

Thank you!

[izaychik63]

The guess button works for me.

[ScottF]

Hi @jeff -

Check the node description panel on the right hand side of your screen for more info on the the syntax (M = month, y = year, etc) - you might have to scroll down to see all the details. As you have already surmised, the format options are case sensitive, so you have to be careful.

As @izaychik63 pointed out, the “Guess data type and format” button is there, but it will only work for formats that it knows about. So, now that you have provided a new format by manually typing it in, it should be able to guess that properly when you configure the node in the future.