Bug #89469 | <= condition is not working in select query for date column. | ||
---|---|---|---|
Submitted: | 30 Jan 2018 11:00 | Modified: | 6 Feb 2018 22:45 |
Reporter: | sheetal umathe | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5/5.6/5.7/8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[30 Jan 2018 11:00]
sheetal umathe
[30 Jan 2018 11:33]
MySQL Verification Team
Thank you for the bug report. Please provide a complete test case: server version, create table, insert data, queries with real result and expected one. Thanks in advance.
[30 Jan 2018 15:06]
Peter Laursen
Isn't this test case enough: SELECT '2017-12-30' >= '11-30-2017'; -- returns "1" SELECT '2017-12-30' <= '11-30-2017'; -- returns "0" ?? -- Peter -- not a MySQL/Oracle person
[30 Jan 2018 15:08]
Peter Laursen
Server version 5.6.34, BTW.
[30 Jan 2018 15:10]
Peter Laursen
Sorry! Please correct the statements to: SELECT '2017-11-30' >= '11-30-2017'; -- returns "1" SELECT '2017-11-30' <= '11-30-2017'; -- returns "0" -- Peter
[1 Feb 2018 11:21]
MySQL Verification Team
Thank you for the feedback.
[6 Feb 2018 22:45]
Roy Lyseng
As far as I understand, this is not a bug. The reproduction case is a bit brief, but I think this is what is requested here: Create a table with a DATE column and insert a few rows: CREATE TABLE t (d DATE); INSERT INTO t VALUES (DATE '2017-11-29'), (DATE '2017-11-30'), (DATE '2017-12-01'); Now, the SQL way to specify DATE literal values is with the keyword DATE followed by a text string on the format 'YYYY-MM-DD'. Thus, we can query our DATE column like this: SELECT * FROM t WHERE d >= DATE '2017-11-30'; +------------+ | d | +------------+ | 2017-11-30 | | 2017-12-01 | +------------+ SELECT * FROM t WHERE d <= DATE '2017-11-30'; +------------+ | d | +------------+ | 2017-11-29 | | 2017-11-30 | +------------+ MySQL also allows abbreviated DATE constants: A literal string may be converted into a DATE literal when compared against a DATE expression. Thus, these two queries are equivalent to the two above: SELECT * FROM t WHERE d >= '2017-11-30'; +------------+ | d | +------------+ | 2017-11-30 | | 2017-12-01 | +------------+ SELECT * FROM t WHERE d <= '2017-11-30'; +------------+ | d | +------------+ | 2017-11-29 | | 2017-11-30 | +------------+ But, any other format on the DATE string is not accepted. See what happens with a DATE value on the format MM-DD-YYYY: SELECT * FROM t WHERE d >= '11-30-2017'; +------------+ | d | +------------+ | 2017-11-29 | | 2017-11-30 | | 2017-12-01 | +------------+ 3 rows in set, 1 warning (0,00 sec) SELECT * FROM t WHERE d <= '11-30-2017'; Empty set, 1 warning (0,00 sec) This can be explained if we display the warning: mysql> show warnings; +---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1292 | Incorrect date value: '11-30-2017' for column 'd' at row 1 | +---------+------+------------------------------------------------------------+