Doubt in SQL query "between" or "in"

Asked

Viewed 580 times

1

I am generating a report between date, as the figure below:

inserir a descrição da imagem aqui

However, when I enter the starting date:

Initial year: 2014 Initial month: 6 and

Final year: 2015 Final month: 5

does not bring the data, the query interprets that initial month 6 is greater than final month 5.

If I use the clause IN the query returns only the months 6 and 5 and not returning the month 4, but when used the BETWEEN does not return any data.

Follow the consultation.

BETWEEN

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

IN

$WHERE= "WHERE v.verba_ano IN($ano,$ano_fnal) AND v.verba_mes IN($mes,$mes_fim)";
mysql sql

  • It’s in PHP, right ?

5 answers

2

The BETWEEN expects the first parameter to be smaller than the second, which causes your problem.

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

Solution

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

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

The day you can generate dynamically from the query:

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

Follow an example on sqlfiddle.

  • gmsantos, a doubt, testing here, I found an error, following: when I insert the initial year: 2014 and the initial month: 06 and the final year: 2015 and the final month 05, the query does not return anything because the initial month is greater than the final month 05 ... any other alternative ? thank you

  • @hulckb you joined the date in only one field ? is of date type?

  • gmsantos, gathered according to its scheme '{$ano}-{$mes}-01' AND last_day('{$ano_fnal}-{$mes_fim}-01'); the type of the func_ano field is INT and the type of func_mes is TINYINT

  • You need to join the fields func_ano with func_mes to a new field of type date, so you will be doing it the right way. Mysql as another database has data types for dates,

  • I’ve already gathered the saints, there’s an example ? hugs

  • I put in the body of the answer, last link

  • Let’s go continue this discussion in chat.

Show 2 more comments

2


Do so (if 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")

  • thanks for the contribution Adir Kuhn

  • If my answer is right for your problem mark as solution, Tks

1

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)

  • Ola Pedro, I tried your tip and it didn’t work out, but thank you so much for contributing

1

Comparing dates is easy if you flip month/year to year/month into a single integer value: In your example, 06/2014 to 05/2015 you would compare whether the date is between 201406 and 201505.

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

Where 201406 = $ano*100+$mes and 201505 - $ano_fim*100+$mes_fim

If you need the day, so does the formula

ano * 10000 + mes * 100 + dia

0

It worked 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')

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.