I have one fields ,the value is like UN-Feb-2021,the year and month is fixed value ,and the day is unknown value ,now I want to change the field to max date .
such as UN-Feb-2021 to 28-Feb-2021
UN-Feb-2020 to 29-Feb-2021
UN-Mar-2020 to 31-Feb-2021
Solved! Go to Solution.
Hi @wu cong? , thank you for your question!
First, we'll replace the 'UN' values with a '01' by using the 'find-replace' transform:
Then, we'll calculate the end-of-month through 2 steps.
The 1st step would be to advance a month for all dates via the 'DATEADD()' function:
The 2nd step uses the same 'DATEADD()' function. This time, we subtract 1 day from the date:
As you can see, the left column is the end-of-month of each of these dates.
There's also a Knowledge-base Article that explores more ways to achieve this (i.e., calculate the end-of-month); check it out!
https://community.trifacta.com/s/article/Calculate-the-end-date-of-a-month
I hope that helps; feel free to ask any additional questions.
Thanks,
Amit.
dear,Amit,thanks very much ,it is a good solution.