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: | |
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 |
[22 Jul 2003 9:00]
Scott Beason
[22 Jul 2003 9:36]
Scott Beason
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.
[22 Jul 2003 11:06]
Sergei Golubchik
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.
[22 Jul 2003 11:17]
Scott Beason
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.
[22 Jul 2003 13:11]
Sergei Golubchik
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
[22 Jul 2003 14:51]
Paul DuBois
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.