0

This question gives us the answer on how to query between 2 dates without using time factor and it is like this

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03'
AND order_date < '2012-05-04'

If we have the following dates

  1. 2012-05-02
  2. 2012-05-04

And we use this

WHERE order_date >= '2012-05-01'
AND order_date < '2012-05-04'

It will only output 2012-05-02 and it will not output 2012-05-04

Question: How can I include 2012-05-04 in my result? I don't want my user to choose 2012-05-05 just to output 2012-05-04.

Community
  • 1
  • 1
Cary Bondoc
  • 2,923
  • 4
  • 37
  • 60

3 Answers3

1
WHERE order_date >= '2012-05-01'
AND order_date < '2012-05-05'

A day has duration of 24 hours, but this can be measured in extremely small units, so 23:59:59 is NOT the final point of the duration. MySQL now does support fractions of seconds so please do not rely on 23:59:59 see http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

The one and only always accurate method is to use LESS THAN THE NEXT DAY.

If you don't want your user to see that next day value simply take the chosen end date but add 1 day to that in your code. e.g.

WHERE order_date >= '2012-05-01'
AND order_date < date_add('2012-05-04',INTERVAL 1 DAY)
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • SQL doesn't have fractional seconds in its times, so `23:59:59` **is** the final point of the duration. – Barmar Apr 14 '16 at 01:10
  • 1
    Incorrect, but it is also simply an unsafe assimption, particularly as there is a workable safe alternative. – Paul Maxwell Apr 14 '16 at 01:12
  • Sorry, didn't know about that recent change. – Barmar Apr 14 '16 at 01:12
  • Oh, I'll remove the downvote. I didn't know about that change also. Sorry. – Cary Bondoc Apr 14 '16 at 01:16
  • This is great! Thank you for saving me. 1 second error is still an error. – Cary Bondoc Apr 15 '16 at 03:32
  • Thank you, providing you with a safe method for filtering a date range is done with great pleasure I assure you. Please advocate it for others who haven't yet seen the light. Note the approach works in all sql dialects but the date arithmetic detail may change. Cheers. – Paul Maxwell Apr 15 '16 at 08:49
0

Change < to <=, because < means that it doesn't include dates that match that date

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-01'
AND order_date <= '2012-05-04'

You can also use BETWEEN

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date BETWEEN '2015-05-01' AND '2012-05-04'

This assumes that the datatype of order_date is DATE, not DATETIME. If it's DATETIME, you need to ensure that the time part is 00:00:00, because that's the default time used when you convert a DATE to a DATETIME. Or you can include an explicit 23:59:59.999999 in the end time in your query, as in Cary Bondoc's answer.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Yeah, confirmed. Your code will work if the case is `DATE`, **not** `DATETIME`. +1! – Cary Bondoc Apr 14 '16 at 01:09
  • MySQL 5.7 supports microseconds, so by assuming 23:59:59 is the finite end point you are building potential bugs into queries, and that is completely avoidable by using less than `the next day `. Furthermore using less than the next day technique applies in any rdbms that has precision finer than 1 second. – Paul Maxwell Apr 14 '16 at 01:27
  • 1
    @Used_By_Already Even though it supports it, you don't get them by default, you have to explicitly give a precision to the `DATETIME` datatype to get fractions. – Barmar Apr 14 '16 at 01:29
  • 1
    And if such a change is applied to columns the bugs will emerge. The very worst aspect of the 23:59:59 assumption us that it produces a 1 second gap and hence some transactions may be completely missed in reports etc. The one and only safe way to do date/time ranges is to use less than the next day. Between does not work either because it uses = on both the lower and upper boundaries. – Paul Maxwell Apr 14 '16 at 01:46
  • 1
    Since he seems to be taking the date range from user input, you should show how to apply your "next day" solution when you're given the end date as a parameter. – Barmar Apr 14 '16 at 01:48
-1

Add a fix time to your query

order_date >= '2012-05-03 00:00:00' AND order_date <= '2012-05-04 23:59:59'

2012-05-03 00:00:00 means that we want to start the search on the start of 2012-05-03

2012-05-04 23:59:59 means that we want to end the search at the end of day of 2012-05-04

So your query would be like this

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03 00:00:00'
AND order_date <= '2012-05-04 23:59:59'
Cary Bondoc
  • 2,923
  • 4
  • 37
  • 60
  • 1
    You need to use `<=`, not `<`. Otherwise it won't match when `order_date = 2012-05-04 23:59:59` – Barmar Apr 14 '16 at 01:11