Bug #45037 Comparing date

Submitted: 22 May 2009 16:25 Modified: 25 May 2009 4:55
Reporter: Riccardo Bettoni Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.32 OS:Windows
Assigned to: CPU Architecture:Any
Tags: date comparison compare greater

[22 May 2009 16:25] Riccardo Bettoni
Description:
Hi.
When I try to compare a datetime field with a date or a datetime in this way

... where date > ('2009-00-00') 

I obtain the same result set of this query

... where date >= ('2009-00-00') 

The problem doesn't appear with < and <= !!

It doesn't matter if you specify also the month or the day of the date or you insert also a time, the problem still appear.

Bye

How to repeat:
CREATE TABLE t(d datetime);
INSERT INTO t ('2009-05-15 15:15:15');
SELECT * FROM t where d > ('2009-00-00 00:00:00');
SELECT * FROM t where d >= ('2009-00-00 00:00:00');
[22 May 2009 17:42] Valeriy Kravchuk
Indeed, I've got the same result:

mysql> SELECT * FROM t where d > ('2009-00-00 00:00:00');
+---------------------+
| d                   |
+---------------------+
| 2009-05-15 15:15:15 | 
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t where d >= ('2009-00-00 00:00:00');
+---------------------+
| d                   |
+---------------------+
| 2009-05-15 15:15:15 | 
+---------------------+
1 row in set (0.00 sec)

But why do you think it is a bug?
[22 May 2009 18:43] Riccardo Bettoni
Effectively maybe is only a problem of interpetation.
I interpret the 00 like null day or month so I think that the result must be a date with a year greater than 2009. Am I wrong?

I know that there is the year function but I must compare dates in that way  because sometimes I know day or month sometimes I don't.
[22 May 2009 19:00] Riccardo Bettoni
Try this also
SELECT * FROM t where d > ('2009-05-15');
[25 May 2009 4:55] Sveta Smirnova
Thank you for the feedback.

'2009-00-00 00:00:00' is definitely lower than '2009-05-15 15:15:15'.

Regarding to '2009-05-15' it converted to DATETIME by adding the time portion as '00:00:00':

cast('2009-05-15' as datetime)
2009-05-15 00:00:00

And '2009-05-15 15:15:15' is greater than '2009-05-15 00:00:00'. So behavior is expected and I close the report as "Not a Bug"

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.