Data truncation: Truncated incorrect date value error

Question

I'm using MySQL version: 5.7.22

So I'm trying to have a table that contains a date column from string. The text field contains data in following DateTime format "d/m/YYYY h:m:s" format. e.g. "14/11/2018 20:10:04 +00:00".

I want to alter the table with a new column that is of the following format '%Y-%m-%d'. I get a

Data truncation: Truncated incorrect date value error

when I try to update the table. But I get the result when I just use a select statement to convert from string to date.

UPDATE BIG_DATA SET BIG_DATA.RealDate = ( SELECT x.d
                                          From (SELECT (DATE_FORMAT(STR_TO_DATE(BIG_DATA.Date , '%d/%m/%Y'), '%Y-%m-%d')) as d
                                          FROM BIG_DATA) as x);

Any help would be grateful!

Rather than having multiple columns for different formats of the same date, have you considered a single [`datetime`](https://dev.mysql.com/doc/refman/5.7/en/datetime.html) column which you [format as needed](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)? — Schwern, Nov 20 '19 at 04:26
I am creating a new column just because I will be needing the original column in the future. — Akash, Nov 20 '19 at 04:28
Could that new column be a `datetime` to avoid having to do this again in the future? — Schwern, Nov 20 '19 at 04:32
Is there a reason you have nested queries 3 deep? Also, what is the datatype for Realdate? If its a date then you dont need to convert your STR_TO_DATE back to a string again. — TomC, Nov 20 '19 at 04:34
@TomC RealDate is in Date format. The reason it is nested because it gave me an error 1093, so I referred this solution https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause — Akash, Nov 20 '19 at 04:38

score 2 · Accepted Answer · answered Nov 20 '19 at 06:09

The reason you are getting an error is that the warning for an incorrect date value (one that produces a value with zeros in it) that is produced by STR_TO_DATE on a SELECT is promoted to an error when you attempt to do an UPDATE. For example, if you do

SELECT STR_TO_DATE('14/11/2018 20:10:04 +00:00', '%d/%m/%Y');
SHOW WARNINGS

Your output will be:

2018-11-14
Warning     1292    Truncated incorrect date value: '14/11/2018 20:10:04 +00:00'

You can work around this by only supplying the date part (the leftmost 10 characters) of the string to STR_TO_DATE:

SELECT STR_TO_DATE(LEFT('14/11/2018 20:10:04 +00:00', 10), '%d/%m/%Y');
SHOW WARNINGS

Output is simply 2018-11-14

This then allows you to create your other column and UPDATE it from the date column:

ALTER TABLE big_data ADD
realdate DATE;
UPDATE big_data
SET realdate = STR_TO_DATE(LEFT(date, 10), '%d/%m/%Y');
SELECT * FROM big_data

Another possibility you might want to consider is using a generated column:

ALTER TABLE big_data ADD
realdate DATE AS (STR_TO_DATE(date, '%d/%m/%Y'));
SELECT * FROM big_data

In both cases the output is

date                        realdate
14/11/2018 20:10:04 +00:00  2018-11-14

Demo on dbfiddle

Data truncation: Truncated incorrect date value error

1 Answers1