将varchar(40)转换为datetime列

问题描述

我有date列，其类型为varchar(40)我想将其转换为datetime

I have date column and it is of type varchar(40)I want to convert it to datetime

请让我知道以下命令是否正确，可以转换为datetime

Please let me know if below command is correct to convert to datetime

UPDATE `table`
SET `date` = str_to_date( `date`, '%d-%m-%Y' );

该表具有数千条记录.例如，date的一条记录的值为10/21/2016 15:02

The table has thousands of records. For example, date has value 10/21/2016 15:02 for one record

推荐答案

两件事:

首先，您的str_to_date()格式必须与输入字符串的格式匹配.如果输入字符串为10/21/2016 15:02，则格式为%m/%d/%Y %H:%i.

First, your str_to_date() format must match the format of the input string. If your input string is 10/21/2016 15:02, then your format is %m/%d/%Y %H:%i.

在此处查看格式代码的参考: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

See a reference for the format codes here: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

第二，使用UPDATE不会更改列的数据类型，它只会更改varchar字符串的内容.

Second, using UPDATE doesn't change the data type of the column, it just changes the content of the varchar string.

mysql> create table t (v varchar(50));

mysql> insert into t values ('10/21/2016 15:02');

mysql> update t set v = str_to_date(v, '%m/%d/%Y %H:%i');

mysql> select * from t;
+---------------------+
| v                   |
+---------------------+
| 2016-10-21 15:02:00 |
+---------------------+

现在它的格式正确，但仍然是varchar.

Now it's in the right format, but it's still a varchar.

mysql> alter table t modify v datetime;

mysql> select * from t;
+---------------------+
| v                   |
+---------------------+
| 2016-10-21 15:02:00 |
+---------------------+

现在数据类型已更改.

mysql> show create table t\G

CREATE TABLE `t` (
  `v` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

这篇关于将varchar(40)转换为datetime列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！