|
Conversion to DATETIME for comparison from other data types works differently, both in terms of the result and in terms of warnings generated.
Summary:
- Implicit conversion never returns warnings
- Explicit conversion always returns a warning
- Implicit conversion always returns zero datetime
- Explicit conversion mostly returns zero datetime, but TIME-to-DATETIME returns NULL
Implicit conversion from TIME to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SET old_mode=ZERO_DATE_TIME_CAST;
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00';
|
+-------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00' |
|
+-------------------------------------------------+
|
| 1 |
|
+-------------------------------------------------+
|
1 row in set (0.00 sec)
|
Implicit conversion from VARCHAR to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SET old_mode=ZERO_DATE_TIME_CAST;
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > '00:00:00';
|
+---------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > '00:00:00' |
|
+---------------------------------------------+
|
| 1 |
|
+---------------------------------------------+
|
1 row in set (0.00 sec)
|
Implicit conversion from INT to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > 0;
|
+------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > 0 |
|
+------------------------------------+
|
| 1 |
|
+------------------------------------+
|
1 row in set (0.00 sec)
|
Explicit conversion from TIME to DATETIME - NULL with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SET old_mode=ZERO_DATE_TIME_CAST;
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME);
|
+-------------------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME) |
|
+-------------------------------------------------------------------+
|
| NULL |
|
+-------------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Explicit conversion from VARCHAR to DATETIME - zero datetime with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SET old_mode=ZERO_DATE_TIME_CAST;
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME);
|
+---------------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME) |
|
+---------------------------------------------------------------+
|
| 1 |
|
+---------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Explicit conversion from INT to DATETIME - zero datetime with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
|
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME);
|
+------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME) |
|
+------------------------------------------------------+
|
| 1 |
|
+------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|