Select between month and year only dates

Question

Good evening, I believe my doubt is easy for some, but I am beginner is breaking my head, I need to do a search with select between the two dates, searching only the month and the year... I did with the month and it worked, now only the year is missing. If you can help me I appreciate.

SELECT * FROM pagamento WHERE Month(data) between '06' AND '07';

It brings the right results between the months, now I need you to look for the year. From now on I thank anyone who can help me.

Answer 1

Assuming the dates are 2010-09-15 and 2020-04-08, you could use DATE_FORMAT to replace the day of the initial date by 01 and the function LAST_DAY to replace the end date day with the biggest day of the month. So you would pick up the records within the past months, regardless of the day.

For example:

SELECT * 
FROM pagamento 
WHERE data between 
    DATE_FORMAT('2010-09-15' ,'%Y-%m-01')  -- ignora o dia e usa dia 01
    AND LAST_DAY('2020-04-08');            -- ignora o dia e usa o último do mês

Answer 2

The DATE_FORMAT() function formats a date as specified

Bank with date in DATETIME format

SELECT * FROM pagamento
where DATE_FORMAT(data,'%Y%m') >= DATE_FORMAT('2010-06-01 00:00:00','%Y%m') and DATE_FORMAT(data,'%Y%m') <= DATE_FORMAT('2020-07-01 00:00:00','%Y%m')

Bank with date in DATE format

SELECT * FROM pagamento
where DATE_FORMAT(data,'%Y%m') >= DATE_FORMAT('2010-06-01','%Y%m') and DATE_FORMAT(data,'%Y%m') <= DATE_FORMAT('2020-07-01','%Y%m')

In both cases you can put any day, and in the case of DATETIME any time the return will be the same, ie selects between the month/year. Must be valid dates, can not put for example day 32

Example with DATE type

Regardless of the days informed

Answer 3

just put the day as first day of the month:

SELECT * FROM pagamento 
WHERE data >= 'YYYY-MM-01' 
AND data <= 'YYYY-MM-01';

Answer 4

Try something like that:

SELECT * FROM PAGAMENTO 
WHERE data >= 'YYYY-MM-DD' 
AND data <= 'YYYY-MM-DD';

Subsititui YYYY MM and DD for Year, Month and Start Day and Year Month and End Day. Usa Day 01 if you want to start the month

Solution without Informing the DIA:

    DELIMITER $

    CREATE PROCEDURE P_Busca(MESI INT, ANOI INT, MESF INT, ANOF INT)
    BEGIN
    SELECT * FROM PAGAMENTO
    WHERE data >= STR_TO_DATE(CONCAT(CAST(ANOI AS CHAR(4)),' ',CAST(MESI AS CHAR(2)),' ','1'),'%Y %c %e')
    AND data <= LAST_DAY(STR_TO_DATE(CONCAT(CAST(ANOF AS CHAR(4)),' ',CAST(MESF AS CHAR(2)),' ','05'),'%Y %c %e'));
    END $

    DELIMITER ;

To Use Procedure Call Her:

CALL P_Busca(Mes Inicial,Ano Inicial,Mes Final, Ano Final);

Example:

CALL P_BUSCA(04,2003,04,2004);

It will be from 04/2003 to 04/2004

Answer 5

Just like you used the function Month to extract the month, you can use the function Year to extract the year, as an example below:

SELECT * FROM pagamento 
WHERE Month(data) between '06' AND '07'
AND Year(data) between '2010' AND '2020';

Documentation of the Year function:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_year