1292 Truncated incorrect date value: '05/18/2011'

Question

I am trying to convert a database with all values stored as VARCHAR into the correct column type eg: INT, DATE, DECIMAL etc...

Problem is, that the columns date_order, date_billed and date_paid are stored in different formats

I still get a 1292 Truncated incorrect date value: '05/18/2011' errors and have pretty much no clue what to do since that exact date format is listed as CASE

My code:

SELECT 
      CAST(`ar_no` AS UNSIGNED), 
      CAST(`accession_id` AS UNSIGNED), 
      CAST(`client_id` AS UNSIGNED), 
      CAST(`insurance_id` AS UNSIGNED), 
      CAST(`test_id` AS UNSIGNED), 

CASE 
     WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
     WHEN LENGTH(DATE(STR_TO_DATE(`date_order`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_order`, '%m/%d/%y') 
     WHEN LENGTH(DATE(STR_TO_DATE(`date_order`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_order`, '%m/%d/%Y')
END,

CASE 
     WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
     WHEN LENGTH(DATE(STR_TO_DATE(`date_billed`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_billed`, '%m/%d/%y') 
     WHEN LENGTH(DATE(STR_TO_DATE(`date_billed`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_billed`, '%m/%d/%Y')
END,

CASE 
     WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
     WHEN LENGTH(DATE(STR_TO_DATE(`date_paid`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_paid`, '%m/%d/%y') 
     WHEN LENGTH(DATE(STR_TO_DATE(`date_paid`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_paid`, '%m/%d/%Y')
END,

CAST(`amount_billed` AS DECIMAL(15,2)),
CAST(`amount_received` AS DECIMAL(15,2)),
CAST(`amount_adjusted` AS DECIMAL(15,2))

FROM `acs`.`billing_unformatted`;

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

Mysql only excepts dates in YYYY-MM-DD format. That is why you are getting errors such as 1292 Truncated incorrect date value: '05/18/2011'

You can change this in Excel before doing your import.

In order to change the date format in excel: right click on the top cell. Choose format cells from the drop down list. change the local to something like 'Afrikans'. Choose the format that looks like 2001-03-14. Use the top cell to fill down. Then save the document.

Just a quick note: Excel sometimes tries to do too much and will revert this column back to a the English(U.S) default time zone. So, if you plan on doing more editing make sure that the column has not reverted back.

Here is a link to more string literals on dev.mysql.

This stored procedure and post might help you as well: Error code 1292

edited May 23 '17 at 10:24

Community

1
1

answered Jul 24 '15 at 23:01

BK435

3,076
3
19
27

the problem is that the entry is 4 Million rows long excel won't open the whole file ... plus I thought the str_to_date() function would convert the wrong date format to the right one https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date – Jeremy Burchwell Jul 24 '15 at 23:42
Yes I use `LOAD DATA INFILE` to import the csv file to a temp table from where I am trying to turn it into a table with the right column types. The stored procedure seems IN general very helpful. I will try to convert it to my case and see if that will help thank you – Jeremy Burchwell Jul 27 '15 at 11:58
I don't have enough points to up vote yet but I marked it as solved – Jeremy Burchwell Jul 27 '15 at 23:25
don't forget about upvoting when you do :)..as well as upvote `Error code 1292`, if you have not noticed, that was a Post I made awhile back...Thanks – BK435 Jul 28 '15 at 00:07

1292 Truncated incorrect date value: '05/18/2011'

1 Answers1