MySQL Bugs: #25706: DATEs seem to be DATETIMEs with an implicit 00:00:00

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

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.

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.

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".

Feedback is still needed. Please, try to repeat with 4.1.22 and/or 5.0.37, and inform about the results.

Duplicate of bugs #16377 & #27590.