Searching dates through BETWEEN AND

Asked

Viewed 8,025 times

4

I have the following situation, where when trying to query a date by another date using BETWEEN, the specified deadline does not return, only "the previous day".

Is there an outline to address this issue?

Follow the dates recorded in the database and the generated Select:

2016-04-12 16:07:03
2016-04-12 16:33:08
2016-04-14 10:47:33
2016-04-14 11:21:14

SELECT * FROM sales.logger where data BETWEEN "2016-04-12" AND "2016-04-14" ORDER BY data;

Return:

2016-04-12 16:07:03
2016-04-12 16:33:08
mysql

  • Already tried, Where data >= "2016-04-12" and data <= "2016-04-14"

  • Have you tried single quote?

4 answers

5


The problem seems to be that you’re treating guys DATETIME as DATE.

Try: 

SELECT * FROM sales.logger where data BETWEEN "2016-04-12 00:00:00" AND "2016-04-14 23:59:59" ORDER BY data;

  • Exelente, It would solve my problem, but for cases like - Where data BETWEEN "2016-04-12 10:00:00" AND "2016-04-14 10:00:00" I would not return the day 14 until 10hrs, which would be the palliative?

  • Hello Matheus, do not know if I understand your comment. If you want all transactions between midnight and 10 am use: where data BETWEEN "2016-04-12 00:00:00" AND "2016-04-14 10:00:00"

  • Hello Anthony, problem solved.

2

You can convert the date

Try: 

SELECT * FROM sales.logger where date(data) BETWEEN "14-04-2016" AND "14-04-2016" ORDER BY data;

  • I joined just to vote, solved the problem easily and quickly, even 4 years after the post...

1

To solve the problem, there are two ways to do using BETWEEN, when using datetime:

SELECT * FROM sales.logger where DATE_FORMAT(data, '%Y-%m-%d')
BETWEEN '2016-04-12' AND '2016-04-14' ORDER BY data;

or

SELECT * FROM sales.logger where data
BETWEEN '2016-04-12 00:00:00' AND '2016-04-14 00:00:00' ORDER BY data;

1

A stable solution for data selection between two dates consists in not using the final date, but rather the day following the end date. When you have a two-date filter on your system, you get the next day from the longest date and on the seat:

SELECT * FROM sales.logger where data >= "2016-04-12" AND data < "2016-04-15";

Note that the lower date is compared with the higher equal, but the final date is compared only with the lower.

That code works:

  • With date fields (YYYY-MM-DD)
  • With date and time fields (YYYY-MM-DD HH:MM:SS)
  • With date, time and fraction fields of any size
    • YYYY-MM-DD HH:MM:SS. F
    • YYYY-MM-DD HH:MM:SS.FF
    • YYYY-MM-DD HH:MM:SS.FFFF
    • YYYY-MM-DD HH:MM:SS.FFFFF
    • YYYY-MM-DD HH:MM:SS.FFFFFF
    • YYYY-MM-DD HH:MM:SS.FFFFFFF
    • ... and so on

It looks like an ugly solution, but generates code that always works, but especially when using code generation tools, where there may be no control of what accuracy of the data field is being used (date, timestamp, timestamp with fraction N).

Browser other questions tagged mysql

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