Truncated incorrect date value

Question

In my situation, I need to deal with invalid date values like: '04070$'. I need to insert something even if I receive erroneous inputs like: '04070$'.

When I do:

select str_to_date('04070$','%m%d%Y') from dual;

The result is:

2000-04-07

But for insert statement, I get an error:

INSERT INTO `table1` ( `Date_Posted`) VALUES (str_to_date('04070$','%m%d%Y'))

#1292 - Truncated incorrect date value: '04070$'

I do not understand why select statement runs fine and insert statement gives error. Is there a way to make insert statement insert NULL or date (even if it is incorrect) when presented with such incorrect values?

Answer 1

It happens because MySQL is running on Strict Mode.

On a SELECT statement, STR_TO_DATE() will return a year, but since it wasn't matched 100%, it'll also issue a warning:

mysql> SELECT str_to_date('040710$','%m%d%y');
+---------------------------------+
| str_to_date('040710$','%m%d%y') |
+---------------------------------+
| 2010-04-07                      |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

As you can see, the warning is just a little mention on the select result, but it's still exists:

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '040710$' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

On Strict Mode, any warning becomes an error when trying to INSERT/UPDATE. that's why you can see a date on select but can't save it on the database.

Answer 2

Change the year format to uppercase like.

str_to_date('01-01-2000','%d-%m-%Y')