Replace Latest Date of the month with First Day of the same month

[Samir_Nagure]

Hi, I want to convert the latest date of every month into the first date of that particular month.

Can someone help me here please?

For Example:

Pasting the data here for your ease.

Current Date Column	Updated Date Column
08-01-22	01-01-22
10-01-22	10-01-22
12-01-22	12-01-22
02-02-22	01-02-22
08-02-22	08-02-22
10-02-22	10-02-22
15-02-22	15-02-22
07-03-22	01-03-22
08-03-22	08-03-22
09-03-22	09-03-22
12-03-22	12-03-22
14-03-22	14-03-22
15-03-22	15-03-22
17-03-22	17-03-22
06-04-22	01-04-22
07-04-22	07-04-22
08-04-22	08-04-22
09-04-22	09-04-22
11-04-22	11-04-22
12-04-22	12-04-22
14-04-22	14-04-22
16-04-22	16-04-22

Regards,
Samir

[aworker]

Hi @Samir_Nagure

Please find below a workflow with a possible solution to your question:

20221230 Pikairos Replace First Date of the month with First Day of same month...

This could be solved in a simpler way using only the -String Manipulation- and the -Lag Column- nodes. However, I’m suggesting this solution here to illustrate how to convert strings into dates and vice-versa too.

Hope it helps.

Best
Ael

[Daniel_Weikert]

@aworker awesome solution! Please remind me the string to datetime node requires a specific format right? (E.g. leading zeros, or 4 digits,…)
Sometimes I am a little confused when KNIME is not picking up the date and throws an error. Maybe I should use the node more often ^^
thanks

[aworker]

Hi @Daniel_Weikert !

Thanks for your message and compliments !

@Daniel_Weikert

Please remind me the string to datetime node requires a specific format right? (E.g. leading zeros, or 4 digits,…)

Indeed but the node has a very nice feature in the configuration menu which most of the time manages to guess the right format. Having said this, it not always succeeds and a bit of help beforehand with a -String Manipulator- node might facilitate a lot its success.

I am not a specialist of “Date & Time” conversion (only a DIYer haha ha). However @takbb did a tremendous and remarkable work on implementing a component which understands most of Data&Time formats:

Flexible Date Reader – takbb

Thanks a lot @takbb ! Hope it helps

I take this opportunity to wish you @Daniel_Weikert, @Samir_Nagure, @takbb and to all the Knime Forum Community a very Happy New Year !

Best
Ael

[Daniel_Weikert]

@aworker

e configuration menu which most of the time manages to guess the right format. Having said this, it not always succeeds and a bit of help beforehand with a -String Manipulator- node might facilitate a lot its success.

I was just confused because the node did not want to pick up my 2 digit year so I switched to python node instead ^^

Thanks a lot @aworker to you and all the other valuable contributers as well

[aworker]

@Daniel_Weikert

I was just confused because the node did not want to pick up my 2 digit year so I switched to python node instead ^^

I believe the node is not happy with a 2 digits year which I can understand since it is prone to ambiguity and miss-confusion with months and days. I always rather prefer to convert the year to a 4 digits format.

Best
Ael

[Samir_Nagure]

Hi Ael,

Thanks a lot for your help. It resolved my issue.

Thank you again and Wish You All a Very Happy New Year…

Regards,
Samir