BETWEEN error day 31

Asked

Viewed 68 times

0

Good afternoon, I did some research but I couldn’t find a solution, so I’m asking for help here. My system recovers data released between date ranges using BETWEEN.

Using the query to catch the inverting of all the records of the month of May, he returns all the releases for this month with exception of the records on May 31. SELECT * FROM financeiro_despesas WHERE data_cadastro BETWEEN 2020-05-1 AND 2020-06-31

Can someone explain it to me ? tried in different tables and the same error happened, if change the date of the records of day 31 and put 30, they appear in select.

mysql sql

  • can mount an example with some data here https://www.db-fiddle.com/ ?

  • No need to write solved in the title, just mark the answers as correct that the question will already be identified with such.

1 answer

3


The problem with your query is that quotation marks are missing.

No quotes is done a mathematical operation, but this does not make it does not specifically bring the day 31/05/2020, maybe there is something wrong with the data.

Anyway, here is the query:

SELECT * 
  FROM financeiro_despesas 
 WHERE data_cadastro BETWEEN '2020-05-01' AND '2020-06-31'

And here’s the working example: https://www.db-fiddle.com/f/mEdpsvzLp8eTiAjreEHM4L/0

  • Thanks for your reply, tried on phpmyadmin worked, but in php remains the same situation, loads the data of the other days less that of day 31. $stmt= $conexao->prepare("SELECT * FROM financeiro_despesas WHERE data_cadastro BETWEEN ? AND ?"); $Start date = "2020-05-01"; $End date = "2020-06-31"; $stmt->bind_param("ss", $Start date, $End date); $stmt->run();

  • 1

    As Ricardo said, try: $DataInicial = "'2020-05-01'"; $DataFinal = "'2020-06-31'"

  • 1

    yes, what @anonimo mentioned, or make a parameter prepare before: $datainicial= date('YYYY-mm-ddd', strtotime($DataInicial)); and use this other variable, but just to be clear, the field is of which type in the table?

  • I just tried both ways and it didn’t work, the field is datetime. at first I thought it could be pq only date step, but I did the test by changing to another date and it works, any date works, with execession if the day is 31. the example of my code posted on this link: http://sandbox.onlinephpfunctions.com/code/e200b58347fcf531c6d66a0eddcea52c39524b7

  • 1

    If the field is datetime, do a test with the date like this '2020-05-01 00:00:00' and '2020-06-31 23:59:59' to see if you’re filtering properly

  • Sorry for the delay @Ricardopunctual, the problem has been fixed by adding the time at the initial and final date. Thank you very much

Show 1 more comment

Browser other questions tagged mysql sql

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