MySQL get specific date of last month

I want to select all rows since 20th last month.

I know I can select a date one month ago using SELECT NOW() - INTERVAL 1 MONTH, but how can I set a specific day in that month?

Today is March 28th, I want to select all rows newer than February 20th.

Even if I could set the day directly, if the queried date was 31st that wouldn’t work for February, where instead it should select the last day of that month.

Note: 20th is just an example, the value is dynamically generated somewhere else in the code.

Some examples:

Day: 20th each month
Now: March 28th | Query: February 20th
Now: May 28th | Query: April 20th

Day: 31st each month
Now: March 28th | Query: February 28th // we select last day instead of 31st
Now: May 28th | Query: April 30th // we select last day instead of 31st

The full query would be something like SELECT * FROM sales WHERE date > SET_DAY(20, NOW() - INTERVAL 1 MONTH).

So, what can I replace that SET_DAY with (that also works with shorter months)?

A pretty common use case for this are billing periods, where you want to select all data from within the current billing cycle.

>Solution :

Assuming you have a parameter in the client that is the target day number, and you want to validate it in the sql (making sure you come up with a day inside the previous month), not in the client, I would do:

least(
    date(date_format(current_date - interval 1 month, '%Y-%m-01')) + interval ? - 1 day,
    date(date_format(current_date, '%Y-%m-01')) - interval 1 day
)

One caution: current_date will be the current date in the timezone of the connection, which is controlled by the client; I prefer to always be explicit if I know what timezone I want the day in, like:

date(convert_tz(utc_timestamp(), '+00:00', 'America/Los_Angeles'))

instead of just current_date (both places).