Mysql group by month and year between dates

Question

I have a table called agenda in Mysql like this (the date is in the database in date format, I put in Portuguese format just to make it easier the example):

I need a query that lists the months and years that have registered dates between the day and the day ending, in the example of the table above what would be listed:

01/2020
02/2020
03/2020
04/2020
05/2020
07/2020

Note that the month 06 of 2020 does not appear, because there are no registered days in this month.

I tried something like this:

SELECT * FROM agenda GROUP BY (BETWEEN agenda.dia_inicio AND agenda.dia_termino)

I know there is the function between, but this is not how it is used. How to do?

Answer 1

Try it this way.

SELECT * FROM agenda where (BETWEEN agenda.dia_inicio AND agenda.dia_termino) GROUP BY MONTH(dia_termino), DAY(dia_termino)

Answer 2

The way you are doing it is not correct, first the date format should be "2020-03-03" (YEAR-MONTH-DAY) for either operations with date work. Then you should choose a column to group, up to where I if it is not possible to group by date range, you should do something like:

SELECT * FROM agenda where (BETWEEN agenda.dia_inicio AND agenda.dia_termino) GROUP BY genda.dia_inicio

or

SELECT * FROM agenda where (BETWEEN agenda.dia_inicio AND agenda.dia_termino) GROUP BY genda.dia_termino