|
I set an empty sql_mode and run a query with CAST(AS DATE):
SET sql_mode='';
|
SELECT CAST(20061108.01 AS DATE);
|
+---------------------------+
|
| CAST(20061108.01 AS DATE) |
|
+---------------------------+
|
| 2006-11-08 |
|
+---------------------------+
|
1 row in set (0.00 sec)
|
Notice, the value was returned without any warnings.
Now I set sql_mode to NO_ZERO_IN_DATE and re-run the query:
SET sql_mode=NO_ZERO_IN_DATE;
|
SELECT CAST(20061108.01 AS DATE);
|
+---------------------------+
|
| CAST(20061108.01 AS DATE) |
|
+---------------------------+
|
| 2006-11-08 |
|
+---------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Hmm, a warning was generated. Let's check it:
+---------+------+---------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------+
|
| Warning | 1292 | Truncated incorrect datetime value: '20061108.01' |
|
+---------+------+---------------------------------------------------+
|
Now let's remove fractional digits from the CAST argument:
SET sql_mode=NO_ZERO_IN_DATE;
|
SELECT CAST(20061108 AS DATE);
|
+------------------------+
|
| CAST(20061108 AS DATE) |
|
+------------------------+
|
| 2006-11-08 |
|
+------------------------+
|
1 row in set (0.00 sec)
|
The warning has gone.
So the warning happens:
- only if there are some fractional digits, and
- only if NO_ZERO_IN_DATE is set
This looks wrong. Fractional digit truncation has no any relevance to NO_ZERO_IN_DATE.
CAST(20061108.01 AS DATE) should return the same warnings no matter if NO_ZERO_IN_DATE is set.
Possible ways:
- Return the result silently, without any messages
- Return the result with a NOTE rather than a WARNING
Note, in case of an implicit conversion on INSERT, fractional digit truncation always generates a note, independently from NO_ZERO_IN_DATE flag:
SET sql_mode='';
|
CREATE OR REPLACE TABLE t1 (a DATE);
|
INSERT INTO t1 VALUES (20061108.01);
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------+
|
| Note | 1265 | Data truncated for column 'a' at row 1 |
|
+-------+------+----------------------------------------+
|
SET sql_mode=NO_ZERO_IN_DATE;
|
CREATE OR REPLACE TABLE t1 (a DATE);
|
INSERT INTO t1 VALUES (20061108.01);
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------+
|
| Note | 1265 | Data truncated for column 'a' at row 1 |
|
+-------+------+----------------------------------------+
|
|