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