convert from string to date format

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

1 Like

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

2020-03-02_11-15-22
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

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

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

Did you press the button?

1 Like

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?

1 Like

Try format M/d/yyyy .

1 Like

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…

1 Like

Y should be in small letters.

2 Likes

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!

1 Like

The guess button works for me.

1 Like

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.

2 Likes

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