Change string to date format

[jedrek]

Dears,

I am new using Knime and I am facing below issue.
I have column which contanis date in this format Mar-2019 (actually it is string). I would like to change it to normal date format 2019-03-01
I have formated String to Date&Time with MMM-yyyy in date format

Hovewer I get this error

ERROR String to Date&Time 0:48 Execute failed: Failed to parse date in row '1.0_Row20: Text ‘Jul-2018’ could not be parsed: Unable to obtain LocalDate from TemporalAccessor: {Year=2018, MonthOfYear=7},ISO of type java.time.format.Parsed

[tommy]

Hi @Jedrek and welcome to the KNIME community,

I guess, you would need to attach the day (“01”) to your column and then use the String to Date&Time Node. I tried this by using the String Manipulator before and it works…
Z_010_data01.knwf (7.6 KB)

regards, Tommy

[jedrek]

Hi @tommy,
Thank you for replay. Unfortunately it didn’t work, because some row contain NULL values. Is it possible to add if statement to String Manipulation?

[tommy]

hi @jedrek,
not in the String Manipulation, however you may use a Row splitter (Row filter) Node in order to exclude the NULL values first.

Top Output Port contains valid rows, Bottom Output Port the “Nulls” / missing values.

[ipazin]

Hi there @jedrek,

also you can unckeck Fail on error option in String to Date&Time node. This will create missing value from any value that can not be parsed.

Br,
Ivan

[jedrek]

hi @ipazin
Thank you for your replay, it has worked but I got “?” instead of empty values.
Can I get NULLs insted?

I have tried to use string manipulation, but “?” are still there
replace($Month$,"?","")

[ipazin]

Hi @jedrek,

to deal with missing values there is Missing Value node so check him out. Also you can use Rule Engine node with following expressions:

MISSING $column_name$ => “NULL”
TRUE => $column_name$

Don’t think String Manipulation node can deal with missing values.

Br,
Ivan

[jedrek]

Thank you, apparently after saving file, there were no “?” Just empty values

[ipazin]

Hi,

red question mark in KNIME means missing value

Br,
Ivan