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

Change the year format to uppercase like.

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

Answer 2

Although your question is valid . But keep in mind that this is a warning not error.

Row is also inserted in this case. I cross checked it with an example and it works fine.

As MySQL always truncate the column value after matching with the appropriate datatype and truncate the unmatched data or out of range values from the complete string.

Example : If you want to insert 'ABCDEFGHIHJHJ' in char(3), it truncates the string and insert ABC in the row.

Answer 3

The Select Statement as well as the Insert statement will give out error, since the $ wont be considered as a number. It has to be

INSERT INTO `table1` ( `Date_Posted`) VALUES (str_to_date('040709','%m%d%Y')).

for inserting into table 1 with the entry 2009-04-07