MySQL Bugs: #889: BETWEEN has issues with a DATE type

Bug #889	BETWEEN has issues with a DATE type
Submitted:	22 Jul 2003 9:00	Modified:	22 Jul 2003 13:11
Reporter:	Scott Beason	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.0.13-nt and 3.23.51-nt	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
I have ran into problems with the BETWEEN operator and dates.  

The manual says that "expr BETWEEN min AND max" means "(min <= expr AND expr <= max)", but this apparently does not hold true for dates...  Apparently NOW() does not fall between the range of one day ago AND today.  (See Example Below)

How to repeat:
Execute the following queries...

SELECT NOW() BETWEEN "2003-05-09" AND "[Current Date]"
It will return 0 (False)

But

SELECT NOW() BETWEEN "2003-05-09" AND "[Current Date + 1 Day]"
It will return 1 (True)

Suggested fix:
Make the function work with the current date OR add a note to the documentation about this and how to fix it (by adding one day -- which, personally, I think would be a big problem).

Even better, try this query out:

SELECT NOW() BETWEEN "2003-01-01" AND CURDATE()
It'll return false

Here's a workaround:

SELECT CURDATE() BETWEEN "2003-01-01" AND "2003-07-22" works okay, but I would think that NOW() is between jan 1 and today...  I guess not.

Again, this isn't a huge bug, but its something that's kinda weird.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

mysql> SELECT NOW() BETWEEN "2003-01-01" AND CURDATE();
+------------------------------------------+
| NOW() BETWEEN "2003-01-01" AND CURDATE() |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.12 sec)

mysql> SELECT NOW(),"2003-01-01",CURDATE();
+---------------------+------------+------------+
| NOW()               | 2003-01-01 | CURDATE()  |
+---------------------+------------+------------+
| 2003-07-22 20:03:29 | 2003-01-01 | 2003-07-22 |
+---------------------+------------+------------+
1 row in set (0.00 sec)

Obviously,

("2003-01-01" <= "2003-07-22 20:03:29" AND "2003-07-22 20:03:29" <= "2003-07-22")

is FALSE. So is BETWEEN.

So, if you have...

d1 = "2003-07-21"
d2 = "2003-07-22"

SELECT NOW() BETWEEN d1 AND d2

and it returns 0 ...  

I fail to see how NOW() (2003-07-22 13:17:32) IS NOT in that range.

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Because, as you wrote, a BETWEEN b AND c is    (b <=a AND a <=c)

and

"2003-07-22 13:17:32" > "2003-07-22"

because "2003-07-22" does NOT mean "2003-07-22 23:59:59"

if you want to compare dates only, you have to take date part of the timestamp for comparison

The reason that NOW() is not in that range is that it has a time
part.  Consider this query:

mysql> SELECT NOW(), CURDATE(), NOW() <= CURDATE();
+---------------------+------------+--------------------+
| NOW()               | CURDATE()  | NOW() <= CURDATE() |
+---------------------+------------+--------------------+
| 2003-07-22 16:50:05 | 2003-07-22 |                  0 |
+---------------------+------------+--------------------+

CURDATE() has an implicit time part of 00:00:00, so it's less
than every other time during that same day except the
first second of the day.