| 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"
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');