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: | |
| 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 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.
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');