How to select only completed months of data where date is in format YYYY-MM?

Question

I have created a query to generate some dates:

create or replace temporary view dates as
   select date_format(add_months(now(), -2), 'y-MM') as date union
   select date_format(add_months(now(), -1), 'y-MM') as date union
   select date_format(add_months(now(), 0), 'y-MM') as date
;

If I run:

select * from dates

I will get:

#   date
1   2022-08
2   2022-09
3   2022-10

I would like to return only completed (whole) months. E.g.

If date_format(add_months(now(), 0), 'y-MM') is 2022-10:

select * from dates
where ???

Should return 2022-08 and 2022-09.

How can I do this?

Answer 1

The following worked for me:

select * 
from 
   dates d
where
   date_format(d.date, 'y-MM') <= date_format(add_months(now(), -1), 'y-MM')

#   date
1   2022-09
2   2022-08