String to Date&Time Node Format MM.yyyy

[Residentstiefel]

Dear community,

I have problems assigning a Date Format to my Date Column, which has the string format MM.yyyy -> e.g. 12.2017
However, the string to date&time node fails transforming it:

ERROR String to Date&Time 0:615 Execute failed: Failed to parse date in row 'Row0#0: Text ‘12.2017’ could not be parsed: Unable to obtain LocalDate from TemporalAccessor: {MonthOfYear=12, Year=2017},ISO of type java.time.format.Parsed

The settings were set like:

Someone encountered the same issue and has a solution?

Thanks and best regards,

Residentstiefel

[RolandBurger]

Hi @Residentstiefel,

You can work around this by first adding a day using String Manipulation, e.g. join(“01.”, $yourVar$). Next, use dd.MM.yyyy in the String to Date&Time.

Cheers,
Roland

[Residentstiefel]

Thanks Roland Burger,

As a final result, I would however need again a MM.yyyy format. Can I manipulate dates in the way to delete the “dd.” and then get back to MM.yyyy ?

[RolandBurger]

That is not possible unfortunately - in order to be considered a date, a day has to be included as well.

Do you have a specific reason why you want to convert it to date&time format? If you don’t plan to use it with some of the other date&time nodes, you might be fine if you just keep your date as a string.

[Residentstiefel]

First of all thanks for your answer.

Unfortunately, the extracted date always refers to the last working day of the month, hence it makes it a bit more complicated. Nevertheless, it is stored in our Database with the format MM.yyyy and for our BI - Tools, it’s quite difficult to get a correct sort on strings.

But your answer helped and we are going to think about a switch to a dd.MM.yyyy format.