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)
2017-07-03
2017-07-15
2017-07-18

will be transformed to
2017-07-01
2017-07-01
2017-07-01

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")

:blush:

4 Likes

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”).

Regards,
Simon

2 Likes

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?
Example
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:
https://www.regular-expressions.info/

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:
\d{4}-\d{2}-\d{2}
So if you need to change the month, an straightforward expression would be this:
regexReplace($date$, "-\\d{2}-", "-01-")

:blush:

8 Likes

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