Alice Sherepa added a comment - 2019-01-03 13:49 There is a warning after truncation anyway, but inside the function server returns error. MariaDB [test]> CREATE or replace FUNCTION f1 () RETURNS date RETURN (select date ("2018-07-26aaa")); Query OK, 0 rows affected (0.006 sec)   MariaDB [test]> show create function f1; +----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | f1 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS date RETURN (select date ("2018-07-26aaa")) | utf8 | utf8_general_ci | latin1_swedish_ci | +----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec)   MariaDB [test]> SELECT f1(); ERROR 1292 (22007): Truncated incorrect date value: '2018-07-26aaa' Error (Code 1292): Truncated incorrect date value: '2018-07-26aaa' Note (Code 4094): At line 2 in test.f1   MariaDB [test]> SELECT date ("2018-07-26aaa"); +------------------------+ | date ("2018-07-26aaa") | +------------------------+ | 2018-07-26 | +------------------------+ 1 row in set, 1 warning (0.000 sec) Warning (Code 1292): Truncated incorrect date value: '2018-07-26aaa'   MariaDB [test]> show variables like 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [test]> set sql_mode=''; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> CREATE or replace FUNCTION f1 () RETURNS date RETURN (select date ("2018-07-26aaa")); Query OK, 0 rows affected (0.006 sec)   MariaDB [test]> SELECT f1(); +------------+ | f1() | +------------+ | 2018-07-26 | +------------+ 1 row in set, 1 warning (0.001 sec)   Warning (Code 1292): Truncated incorrect date value: '2018-07-26aaa'

Alexander Barkov added a comment - 2019-04-29 04:41 - edited MariaDB does not support yet timestamp format with time zone. This will be done in MDEV-11829 . So the warning happens because of the '+03:00' part in the timestamp string. If you remove the '+03:00' part it works without warnings or errors: SET NAMES 'utf8' ; SET sql_mode= DEFAULT ; DELIMITER $$ CREATE OR REPLACE FUNCTION f1 () RETURNS date BEGIN RETURN ( SELECT date (JSON_VALUE( '{"b":"2018-07-26T00:00:00"}' , '$.b' ))); END $$ DELIMITER ; SELECT date (JSON_VALUE( '{"b":"2018-07-26T00:00:00"}' , '$.b' )) AS c1, f1(); +------------+------------+ | c1 | f1() | +------------+------------+ | 2018-07-26 | 2018-07-26 | +------------+------------+ Possible workarounds: Use empty sql_mode at the function creation time This will suppress converting warnings to errors inside the stored function: SET NAMES 'utf8' ; SET sql_mode= '' ; DELIMITER $$ CREATE OR REPLACE FUNCTION f1 () RETURNS date BEGIN RETURN ( SELECT date (JSON_VALUE( '{"b":"2018-07-26T00:00:00+03:00"}' , '$.b' ))); END $$ DELIMITER ; SET sql_mode= DEFAULT ; -- Execution time sql_mode is not important SELECT date (JSON_VALUE( '{"b":"2018-07-26T00:00:00+03:00"}' , '$.b' )) AS c1, f1(); +------------+------------+ | c1 | f1() | +------------+------------+ | 2018-07-26 | 2018-07-26 | +------------+------------+ 1 row in set, 2 warnings (0.001 sec) Remove the '+03:00' part before passing the value to date() The warning will disappear. If can be LEFT(datestr, 19) in simple cases, or REGEXP_SUBSTR(datexpr,'...') in more complex cases. SET NAMES 'utf8' ; SET sql_mode= DEFAULT ; DELIMITER $$ CREATE OR REPLACE FUNCTION f1 () RETURNS date BEGIN RETURN ( SELECT date ( LEFT (JSON_VALUE( '{"b":"2018-07-26T00:00:00+03:00"}' , '$.b' ),19))); END $$ DELIMITER ; SELECT date ( LEFT (JSON_VALUE( '{"b":"2018-07-26T00:00:00+03:00"}' , '$.b' ),19)) AS c1, f1(); +------------+------------+ | c1 | f1() | +------------+------------+ | 2018-07-26 | 2018-07-26 | +------------+------------+ 1 row in set (0.001 sec)