Mysql STR_TO_DATE give inconsistent result across different environment

user_247099 0
Viewed 55

I am running into an inconsistent behaviour across two different Docker image with the STR_TO_DATE function. On running on a ubuntu box and the other one running on my Mac.

On Linux:

SELECT STR_TO_DATE('203005','%H%i%s');
+--------------------------------+
| STR_TO_DATE('203005','%H%i%s') |
+--------------------------------+
| 20:30:05                       |
+--------------------------------+

SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

On my Mac:

SELECT STR_TO_DATE('203005','%H%i%s');
+--------------------------------+
| STR_TO_DATE('203005','%H%i%s') |
+--------------------------------+
| NULL                           |
+--------------------------------+

SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.12    |
+-----------+

show warnings; 
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '203005' for function str_to_date |
+---------+------+-------------------------------------------------------------+

Both running Mysql on a Docker image:

      docker_container:
        name: mysql
        image: mysql:8.0
docker date mysql
1 Answers

STR_TO_DATE will return NULL if you have an incomplete datetime value (in your case, there's no date) and SQL MODE NO_ZERO_DATE is set (see the manual). For example:

SET @@sql_mode = ''
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date
;
SET @@sql_mode = 'NO_ZERO_IN_DATE'
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date
;
SET @@sql_mode = 'NO_ZERO_DATE'
;
SELECT @@sql_mode, STR_TO_DATE('203005','%H%i%s') AS date

Output:

@@sql_mode          date
                    20:30:05
NO_ZERO_IN_DATE     20:30:05
NO_ZERO_DATE        null

Demo on dbfiddle

user_94737640
Related