Doubt in SQL query "between" or "in"

Do this (if it's php and mysql)

WHERE STR_TO_DATE(CONCAT_WS('-', v.verba_ano, v.verba_mes), "%Y-%m") BETWEEN STR_TO_DATE('{$ano}-{$mes}', "%Y-%m") AND STR_TO_DATE('{$ano_fim}-{$mes_fim}', "%Y-%m")

The BETWEEN expects the first parameter to be less than the second, which causes its problem.

Using IN also does not solve your problem because it does not work with ranges, but with isolated values. Ex: data IN (3,8) will not bring the range 3 to 8, but rather exactly values 3 and 8

Solution

Change your query to select the complete date, in AAAA-MM-DD format.

Always try to store date columns with type DATE in your database.

The day you can dynamically generate from the query:

SELECT * FROM tabA
WHERE verba_data BETWEEN '{$ano}-{$mes}-01' AND last_day('{$ano_fnal}-{$mes_fim}-01');

Here's an example in sqlfiddle .

Try something like that, it worked for me.

WHERE (v.verba_ano BETWEEN $ano AND $ano_fnal) AND (v.verba_mes BETWEEN $mes AND $mes_fim)

Compare dates is easy if you reverse month / year to year / month in a single integer value: In your example 06/2014 to 05/2015 you would compare if the date is between 201406 and 201505.

where (verba_ano * 100 + verba_mes) between 201406 and 201505

Where 201406 = $ year * 100 + $ month and 201505 - $ year_fin * 100 + $ month_fin

If you need the day, the formula is also

ano * 10000 + mes * 100 + dia

It worked right here, follow the code below:

AND STR_TO_DATE(CONCAT_WS('-',i.func_ano,i.func_mes,'01'),'%Y-%m-%d')
BETWEEN STR_TO_DATE('$ano-$mes-01','%Y-%m-%d') 
AND STR_TO_DATE('$ano_fnal-$mes_fim-01','%Y-%m-%d')