Convert Invalid dates to Valid dates

Shaaz

Hi All,

I've a data like below.

Date

2020-01-01

2021-04-30

2021-12-31

2021-02-31

How do I get below expected output

Date

2020-01-01

2021-04-30

2021-12-31

2021-02-28

Here logic is since we don't get 31st Feb 2021, it should fall back to last day of month of that particular month (like here it is 2021-02-28) and it has to be dynamic as I might dates likes 31st Nov, 31st Sep or 45th Dec.

philipmannering

Perhaps this formula works

min(datetimetrim(left(date, 7) + '-01', 'lastofmonth'), todate([date]))

But it does give you a warning in the messages, so there's bound to be a better solution.