How to make addMonth('2022-02-28', 1) to be '2022-03-31'?

[Alex-Cheng]

I want a new function which would add month to the date of last day of a month, resulting a date which is also the last day of a month. E.g. addMonth('2022-02-08', 1) returns '2022-03-31'.

Thank you.

[UnamedRus]

SELECT toEndOfMonth(addMonths(toDate('2022-02-08'), 1)) AS res

#34394

[Alex-Cheng]

Thank you. But I need to check whether the input date is the end of month first. Alternatively I implemented a new function addMonthsPro which modified the code a little bit.

    template <typename DateOrTime>
    inline LUTIndex NO_SANITIZE_UNDEFINED addMonthsIndex_pro(DateOrTime v, Int64 delta) const
    {
        const Values & values = lut[toLUTIndex(v)];

        Int64 old_year = values.year;
        Int64 old_month = values.month;
        Int64 month = values.month + delta;

        if (month > 0)
        {
            auto year = values.year + (month - 1) / 12;
            month = ((month - 1) % 12) + 1;
            auto day_of_month = saturateDayOfMonth_pro(year, month, old_year, old_month, values.day_of_month);

            return makeLUTIndex(year, month, day_of_month);
        }
        else
        {
            auto year = values.year - (12 - month) / 12;
            month = 12 - (-month % 12);
            auto day_of_month = saturateDayOfMonth_pro(year, month, old_year, old_month, values.day_of_month);

            return makeLUTIndex(year, month, day_of_month);
        }
    }

    inline UInt8 saturateDayOfMonth_pro(Int16 year, UInt8 month, Int16 old_year, UInt8 old_month, UInt8 day_of_month) const
    {
        if (likely(day_of_month <= 27))
            return day_of_month;

        UInt8 days_in_old_month = daysInMonth(old_year, old_month);
        UInt8 days_in_month = daysInMonth(year, month);

        if (day_of_month > days_in_month)
            day_of_month = days_in_month;

        if (day_of_month == days_in_old_month)
            day_of_month = days_in_month;

        return day_of_month;
    }