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.