how to get the max date about partial date?

[WuCong]

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

[AMiller_Tri · Accepted 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.

[WuCong]

dear,Amit,thanks very much ,it is a good solution.