In Mysql. finding every 6 months last date record-CodePudding

I am new to SQL. I have 5 years of data. and I want to retrieve every six months last date data. let's assume today's date is 2022-12-20.

Date   
2022-12-20
2022-06-30
2021-12-31
2021-06-30
2020-12-31

I want every six months' last date data. till 2017.

CodePudding user response：

with recursive mydates (dt) as (
  select sysdate()
  union all
  select last_day(date_add(d.dt, interval -6 month))
  from mydates d
  where d.dt >= date_add(sysdate(), interval -5 year)
)
select date(dt) as dt
from mydates
order by 1 desc