Home / Questions / 490450
Accepted
Damian Garcia
Asked: 2022-03-14 17:37:31 +0800 CST 2022-03-14 17:37:31 +0800 CST

MySQL query to fetch records between a range of months of a particular year

  • 772

I have a MySQL query in which I want to fetch all records from a particular year and then export and import it locally. I would have liked to bring it for the year only, but since there were so many records (when I wanted to import it, it wouldn't let me because of its size) I had to split it into two parts, in months (01 to 06 and 07 to 12).

The query I made in the first half of the year between months 01 to 06 was done well, the problem was with the other half (07 to 12). The Date data type is datetime . The query, which is very simple, I did it like this:

select * from `estadistica` where Fecha BETWEEN '2020-07-01' and '2020-12-31'

And in this case, I don't know why, it only brings me up to the 30th of the 12th month (12/30), that is, it rules out the 12/31. And it's not that there are no records, there are, because I did a common query and it shows data from day 31.

I have also tried this other query (putting HH:MM:SS):

select * from estadistica where Fecha BETWEEN '2020-07-01 00:00:00' and '2020-12-31 00:00:00'

and the same thing happens, it only brings me until December 30 .

mysql

2 Answers

  1. Best Answer

    As the column is Fechaof type datetime, if the record time is different from the start of the day, your query will not include it in the result, because the deadline '2020-12-31' is equal to '2020-12-31 00:00 :00' (therefore said record would exceed it).

    The simplest thing to avoid having to deal with the time (since in this case you don't use it to restrict) is to cast the column Fechato type date(this way you make sure to include all records with date '2020-12-31' regardless of the time they have):

    SELECT * 
FROM `estadistica`
WHERE CAST(Fecha AS date) BETWEEN '2020-07-01' AND '2020-12-31';
    • 2

  2. I hope to help you if your columns are of type timestamp, that could be the error, I would suggest that you only add hours to your query.

    select * from estadistica where Fecha BETWEEN '2020-07-01 00:00:00' and '2020-12-31 59:59:00'
    • 0