0

I have never run into such an issue before. Was given a bunch of data and told to compile it into some tables and develop some reports. The users who gathered the data used various different date formats which has never been an issue in the past for me. So I import all the data into some temp tables and generate a SELECT query which converts the different date formats into yyyy-mm-dd dates using the str_to_date() in order to be imported into a date column. I like what I see, all the data types and column names match so I add the SELECT query to an INSERT statement and receive the following:

Error Code: 1411. Incorrect datetime value: '2012-10-31' for function str_to_date.

The column being inserted into is a Date data type not a DateTime. I did try to add the time part to these dates but received exactly the same error message. I have attempted everything I can possibly think of. I am about to export to a csv file and import just so I can move on with my life but something isn't right. I have used functions like str_to_date and to_date (sqlserver) for a long time. Why is the SELECT query returning without error or warning but the INSERT INTO SELECT throwing this error?

I have changed sql_mode, added ALLOW_INVALID_DATES, removed NO_ZERO_DATE and NO_ZERO_IN_DATE. Hell, I set sql_mode = ''! I have looked for ascii characters in the data. Crazy thing is if I copy and past the returned data from the select query into str_to_date(datestring, '%m/%d/%Y') <--or whatever the expected format and issue an INSERT, it works!

Here is the query that has me rethinking my life choices:

INSERT INTO leases_new (leases_new.id, 
                        leases_new.payment_start_date
                        )
    SELECT lease_import_data_view.id, 
            case
                when UPPER(lease_import_data_view.lease_start_date) like '%EASE%'
                    then STR_TO_DATE('1899-01-01', '%Y-%m-%d')
                when UPPER(lease_import_data_view.lease_start_date) like '%MOL%'
                    then STR_TO_DATE('2013-12-01', '%Y-%m-%d')
                else case
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%m/%d/%Y') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%m/%d/%Y')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%c/%e/%Y') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%c/%e/%Y')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%m%d%Y') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%m%d%Y')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y/%m/%d') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y/%m/%d')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y/%c/%e') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y/%c/%e')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y%m%d') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y%m%d')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y-%m-%d') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%Y-%m-%d')
                        when STR_TO_DATE(lease_import_data_view.lease_start_date, '%m-%d-%Y') is not null
                            then STR_TO_DATE(lease_import_data_view.lease_start_date, '%m-%d-%Y')
                        else STR_TO_DATE('1899-01-01', '%Y-%m-%d')
                    end
            end as payment_start_date
    FROM lease_import_data_view
    GROUP BY lease_import_data_view.id

Here is the current state of my SQL_MODE

SELECT @@SQL_MODE;

  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES'

I will admit, I am fighting off the flu right now so it is very possible I am overlooking something very obvious. Does anyone know what might be going on here? Help with this is greatly appreciated.

Improve this question
3
  • What MySQL version are you using?
    – Mauricio Cacho
    Nov 10, 2022 at 1:06
  • 2
    There are organizations that use %d/%m/%Y -- Your CASE cannot distinguish that from %m/%d/%Y.
    – Rick James
    Nov 10, 2022 at 2:26
  • I was thinking about that one Rick. Dates like 1/11/2022, how would the system know the difference. I think I will need to exclude those from the insert and pick through them using something like "month_due" or something from the "notes" column. It's kind of a mess. Thanks for pointing that out.
    – waltmagic
    Nov 10, 2022 at 15:56

1 Answer 1

Reset to default
1

I did some tests trying to replicate your error, and managed to find "Warnings" instead, I'm not sure if this can lead to you finding the root cause but I hope this helps:

I created a random table and inserted the value you are having trouble with:

mysql> CREATE TABLE tests.texto(somet VARCHAR(50));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tests.texto VALUES ("2012-10-31");
Query OK, 1 row affected (0.01 sec)

Then, I executed a similar SELECT, with the cases:

mysql> SELECT CASE
    -> when STR_TO_DATE(somet, '%m/%d/%Y') is not null
    ->     then STR_TO_DATE(somet, '%m/%d/%Y')
    -> when STR_TO_DATE(somet, '%c/%e/%Y') is not null
    ->     then STR_TO_DATE(somet, '%c/%e/%Y')
    -> when STR_TO_DATE(somet, '%m%d%Y') is not null
    ->     then STR_TO_DATE(somet, '%m%d%Y')
    -> when STR_TO_DATE(somet, '%Y/%m/%d') is not null
    ->     then STR_TO_DATE(somet, '%Y/%m/%d')
    -> when STR_TO_DATE(somet, '%Y/%c/%e') is not null
    ->     then STR_TO_DATE(somet, '%Y/%c/%e')
    -> when STR_TO_DATE(somet, '%Y%m%d') is not null
    ->     then STR_TO_DATE(somet, '%Y%m%d')
    -> when STR_TO_DATE(somet, '%Y-%m-%d') is not null
    ->     then STR_TO_DATE(somet, '%Y-%m-%d')
    -> when STR_TO_DATE(somet, '%m-%d-%Y') is not null
    ->     then STR_TO_DATE(somet, '%m-%d-%Y')
    -> end as fecha
    -> FROM tests.texto;
+------------+
| fecha      |
+------------+
| 2012-10-31 |
+------------+
1 row in set, 6 warnings (0.00 sec)

You can see that, even though the select executes, I have 6 warnings: mys

ql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
+---------+------+-----------------------------------------------------------------+
6 rows in set (0.00 sec)

And the same happens when I turn that into an INSERT (to another dummy table).

mysql> CREATE TABLE tests.test(something date);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tests.test
    -> SELECT CASE
    -> when STR_TO_DATE(somet, '%m/%d/%Y') is not null
    ->     then STR_TO_DATE(somet, '%m/%d/%Y')
    -> when STR_TO_DATE(somet, '%c/%e/%Y') is not null
    ->     then STR_TO_DATE(somet, '%c/%e/%Y')
    -> when STR_TO_DATE(somet, '%m%d%Y') is not null
    ->     then STR_TO_DATE(somet, '%m%d%Y')
    -> when STR_TO_DATE(somet, '%Y/%m/%d') is not null
    ->     then STR_TO_DATE(somet, '%Y/%m/%d')
    -> when STR_TO_DATE(somet, '%Y/%c/%e') is not null
    ->     then STR_TO_DATE(somet, '%Y/%c/%e')
    -> when STR_TO_DATE(somet, '%Y%m%d') is not null
    ->     then STR_TO_DATE(somet, '%Y%m%d')
    -> when STR_TO_DATE(somet, '%Y-%m-%d') is not null
    ->     then STR_TO_DATE(somet, '%Y-%m-%d')
    -> when STR_TO_DATE(somet, '%m-%d-%Y') is not null
    ->     then STR_TO_DATE(somet, '%m-%d-%Y')
    -> end as fecha
    -> FROM tests.texto;
Query OK, 1 row affected, 6 warnings (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 6

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
| Warning | 1411 | Incorrect datetime value: '2012-10-31' for function str_to_date |
+---------+------+-----------------------------------------------------------------+
6 rows in set (0.00 sec)

But the "correct" record was inserted:

mysql> SELECT * FROM tests.test;
+------------+
| something  |
+------------+
| 2012-10-31 |
+------------+
1 row in set (0.00 sec)

Maybe there's something else modifying the Warning into Error?

Improve this answer
1
  • I solved the issue. I don't "know" for a fact this was causing it but daylight savings just changed not long ago and I don't restart this computer very often. I noticed that when I ran these same queries on my personal laptop it worked. I checked and this work computer was set to CST instead of UTC (I remember changing that for some reason, I can't remember why). I figured OK update mariadb, set time zone to UTC and reset my sql_modes, making sure ALLOW_INVALID_DATES was in there and restart the service. Worked immediately. Had I just rebooted it might have worked. facepalm
    – waltmagic
    Nov 10, 2022 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.