MySQL Convert data VARCHAR to DATETIME

Related searches

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

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

Convert from varchar to datetime and compare in MySQL?, mysql> create table DemoTable1565 -> ( -> ArrivalDatetime varchar(40) -> ); Query OK, 0 rows affected (0.82 sec). Insert some records in the� Convert from varchar to datetime and compare in MySQL? Comparison of varchar date records from the current date in MySQL Order by date set with varchar type in MySQL

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;

Convert varchar to date in MySQL?, You can use date_format() to convert varchar to date. The syntax is as follows − SELECT DATE_FORMAT(STR_TO_DATE(yourColumnName,� DATETIME: Converts value to DATETIME. Format: "YYYY-MM-DD HH:MM:SS" TIME: Converts value to TIME. Format: "HH:MM:SS" CHAR: Converts value to CHAR (a fixed length string) SIGNED: Converts value to SIGNED (a signed 64-bit integer) UNSIGNED: Converts value to UNSIGNED (an unsigned 64-bit integer) BINARY: Converts value to BINARY (a binary string)

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.

MySQL CONVERT() Function, Value, Description. DATE, Converts value to DATE. Format: "YYYY-MM-DD". DATETIME, Converts value to DATETIME. Format: "YYYY-MM-DD HH:MM:SS". In SQL Server, you can use CONVERT function to convert a DATETIME value to a string with the specified format. In MySQL, you can use DATE_FORMAT function. SQL Server: -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds) SELECT CONVERT(VARCHAR, GETDATE(), 121); # 2012-11-29 19:18:41.863

MySQL CAST() Function, MySQL CAST() Function. ❮ MySQL Functions. Example. Convert a value to a DATE datatype: SELECT CAST(� DATETIME: Converts value to DATETIME. Format: "YYYY-MM-DD HH:MM:SS" TIME: Converts value to TIME. Format: "HH:MM:SS" CHAR: Converts value to CHAR (a fixed length string) SIGNED: Converts value to SIGNED (a signed 64-bit integer) UNSIGNED: Converts value to UNSIGNED (an unsigned 64-bit integer) BINARY: Converts value to BINARY (a binary string)

What is the Difference Between CAST and CONVERT?, The CAST and CONVERT functions are both used to convert data from one data type For example, when converting a DateTime datatype to Varchar, you can� I am trying to convert varchar to date time This is what I have now, and it is not working for me. I always get the have value How to get data from Mysql between

The first syntax for the MySQL CONVERT function allows you to convert a value from one datatype to another datatype. Let's look at how to use the CONVERT function to convert a value to a DATE type. For example: mysql> SELECT CONVERT('2014-02-28', DATE); Result: '2014-02-28' This CONVERT example takes the value '2014-02-28' and converts it to a

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.
  • 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.