Bug #25706 DATEs seem to be DATETIMEs with an implicit 00:00:00
Submitted: 18 Jan 2007 20:54 Modified: 24 Apr 2007 18:53
Reporter: Sheeri Cabral (Candidate Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:4.1.19 and 5.0.19 OS:Linux (Fedora Core 4)
Assigned to: CPU Architecture:Any
Tags: qc

[18 Jan 2007 20:54] Sheeri Cabral
Description:
DATEs seem to be DATETIMEs with an implicit 00:00:00

This interferes with queries that use the date as an actual date and expect the date to include everything up until 23:59:59 of that day.  

How to repeat:
The easiest way to reproduce this:

SELECT IF(NOW() BETWEEN '2007-01-17' AND '2007-01-18','yes','no') AS test\G
test: no
1 row in set (0.00 sec)

In fact, the following query always returns "no", unless it's exactly midnight:

SELECT IF(NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(),'yes','no') AS test\G
test: no
1 row in set (0.00 sec)

This does not make logical sense -- NOW() should __ALWAYS__ be between "yesterday" and "today".  It's one thing to be cautious not to compare DATEs and DATETIMEs to each other.  However, this is a bit too much of a bug for me.  And it's not just the BETWEEN...AND syntax -- that just converts to >= and < = to.  For a test of this:

SELECT IF(NOW() >= CURRENT_DATE() - INTERVAL 1 DAY AND NOW() < = CURRENT_DATE(),'yes','no') AS test\G
test: no
1 row in set (0.00 sec)

Again, NOW() should fall between "yesterday" and "today".  

There is a workaround, although poor at best:

SELECT IF(DATE(NOW()) BETWEEN CURRENT_DATE() AND CURRENT_DATE(),'yes','no') AS test\G
test: yes
1 row in set (0.00 sec)

I found this with both MySQL 4.1.19-standard-log and 5.0.19-standard-log.

Suggested fix:
The date should either span the entire day or return an error when attempting to be compared to a time.
[19 Jan 2007 17:51] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer versions, 4.1.22 and/or 5.0.27, and inform about the results.
[20 Feb 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Apr 2007 16:58] Valeriy Kravchuk
Feedback is still needed. Please, try to repeat with 4.1.22 and/or 5.0.37, and inform about the results.
[24 Apr 2007 18:53] Evgeny Potemkin
Duplicate of bugs #16377 & #27590.