Shifting Date and Time to the first of the month

Hi fellow KNIME-rs

Just a quick question.

I have a column with a few dates that I want to change to the first of the month

So for example (YYYY-MM-DD)

will be transformed to

The use case is that invoices that come within the month (ex July 03, July 15 & July 18) are all considered to be in July 01

I’ve tried … Date&Time to string => using a Cell Splitter => changing the days column => Column Combiner => String to Date&Time

However that didn’t work either as the Cell Splitter converts my Strings to Integers therefore dropping the zero then I get an error in the String to Date&Time node.

2017-07-18 becomes 2017-07-1 which causes an error :frowning:

Is there an easy way to do this? Thanks!

Hi @Woodenfootspa,

String Manipulation:

regexReplace($date$, "\\d+$", "01")



Hi @Woodenfootspa,

if you use yyyy-MM-d as the date format in the String to Date&Time node, it will convert successfully dates with single (e.g. “1”) digit well as double digit days (e.g. “01”).



Thanks Simon and Armin!

Hi Armin this works great too.

Can you explain how this works in detail? How can I modify this to change the month instead?
2017-07-16 to 2017-01-16

Is there a tutorial here for coding String Manipulations?

The String Manipulation functions are simple. You do not need a tutorial, just read the descriptions and examples. But in the expression I gave you, I have used Regex. To learn regex, I recommend this website:

Now let’s explain the current expression. The regexReplace() function, takes 3 arguments. A string, a regex pattern and another string. The first string is your main input, then wherever the pattern is found, the second string would replace it.

The regex:
\\d+$ means one or more ( the “+” sign) digital character ("\d" - in string manipulation we need to use an extra \ to escape the original one) which is at the end of (the “$” sign) the string.
Your date format can be converted to regex like this:
So if you need to change the month, an straightforward expression would be this:
regexReplace($date$, "-\\d{2}-", "-01-")



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