0

The table users was badly designed. The created_at and updated_at records were set with the VARCHAR type and the values were placed in them for a long time in the format like 20.10.2017 8:10.

It is necessary to convert fields to the DATETIME type in MySQL, saving the values entered earlier.

The STR_TO_DATETIME function is not suitable because it changes the output format, but not the VARCHAR data type itself to DATETIME data type in the table. 

ALTER TABLE users MODIFY created_at DATETIME

does not work

Error message:

SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: '06.01.2020 8:10' for column 'created_at' at row 1

Improve this question

3 Answers 3

Reset to default
2

When you change a column from VARCHAR to DATETIME, there's no way for you to specify custom parsing of the string, it has to be in MySQL's default format.

So you'll need to do this in multiple steps:

  1. Add a DATETIME column.
  2. Fill in the new column by parsing the VARCHAR column with STR_TO_DATE()
  3. Remove the old column and rename the new column.
ALTER TABLE users ADD created_at_dt DATETIME, updated_at_dt DATETIME;
UPDATE users
SET created_at_dt = STR_TO_DATE(created_at, '%d.%m.%Y %h:%i'),
    updated_at_dt = STR_TO_DATE(updated_at, '%d.%m.%Y %h:%i');
ALTER TABLE users 
    DROP created_at, DROP updated_at, 
    RENAME COLUMN created_at_dt TO created_at, RENAME COLUMN updated_at_dt TO updated_at;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I suggest making sure that the new columns contain valid dates before dropping the original columns. If the STR_TO_DATE() doesn't succeed, it will just store NULLs.
Thank you for the solution, but the task is not to delete or add new columns, but to change the type of existing ones.
You can't change the type in one step and keep the values.
1

The easiest way to work around this is to create new created_at and updated_at DATETIME columns, assign them the parsed date values, and then drop the old columns and rename the new ones. For example:

ALTER TABLE users ADD new_created_at DATETIME
UPDATE users SET new_created_at = STR_TO_DATE(created_at, '%d.%m.%Y %l:%i')
ALTER TABLE users DROP created_at, CHANGE new_created_at created_at DATETIME

Demo on dbfiddle

As @BillKarwin points out, you should check that new_created_at contains valid values before dropping the created_at column just in case some dates do not match the format. You can check for those cases using 

SELECT *
FROM users
WHERE new_created_at IS NULL

And as @Barmar points out, it is preferable to try this first in a backup copy of the table.

Improve this answer

4 Comments

I suggest making sure that the new columns contain valid dates before dropping the original columns. If the STR_TO_DATE() doesn't succeed, it will just store NULLs.
@BillKarwin good point. I've updated the answer to mention that.
And it hardly needs to be mentioned that you should try this first in a backup copy.
@Barmar indeed. That should be obvious, but I've added a note anyway.
0

You date has the false Format convert it to the correct mysql time and then alter the table

select version();

| version() |
| :-------- |
| 8.0.19    |

CREATE TABLE users
    (`created_at` varchar(20))
;

INSERT INTO users
    (`created_at`)
VALUES
    ("06.01.2020 8:10"),
    ("07.01.2020 8:10"),
    ("08.01.2020 8:10"),
    ("09.01.2020 8:10")

✓

✓

SELECT STR_TO_DATE(created_at, "%d.%m.%Y %k:%i") FROM users;

| STR_TO_DATE(created_at, "%d.%m.%Y %k:%i") |
| :---------------------------------------- |
| 2020-01-06 08:10:00                       |
| 2020-01-07 08:10:00                       |
| 2020-01-08 08:10:00                       |
| 2020-01-09 08:10:00                       |

UPDATE users SET created_at = STR_TO_DATE(created_at, "%d.%m.%Y %k:%i");

✓

SELECT * FROM users;

| created_at          |
| :------------------ |
| 2020-01-06 08:10:00 |
| 2020-01-07 08:10:00 |
| 2020-01-08 08:10:00 |
| 2020-01-09 08:10:00 |

ALTER TABLE users MODIFY created_at DATETIME

✓

SELECT * FROM users;

| created_at          |
| :------------------ |
| 2020-01-06 08:10:00 |
| 2020-01-07 08:10:00 |
| 2020-01-08 08:10:00 |
| 2020-01-09 08:10:00 |

db<>fiddle here

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.