无法在MySql中将varchar转换为datetime [英] Unable to convert varchar to datetime in MySql

问题描述

在我的MySql表中有一个名为_time的类型为varchar的列。它所持有的值为格式：年月日小时分钟没有空格： 201409201945 我想转换它到 datetime 所以我这样做：

  ALTER TABLE`my_table `CHANGE COLUMN`_time``date_time` DATETIME NOT NULL; 
  

由于某种原因，它会抛出此错误：

错误代码：1292.不正确的日期时间值：第1行列_date_time的2014092019450.036秒

解决方案

@Arkain提到的三个步骤将在函数 STR_TO_DATE

   - 添加新列
 ALTER TABLE`my_table` ADD COLUMN`date_time` DATETIME; 
 
  - 借助函数STR_TO_DATE 
更新新列更新`my_table` SET`date_time` = STR_TO_DATE（`_time`，'％Y％m％d％H％一世'）; 
 
  - 删除旧列
 ALTER TABLE`my_table` DROP COLUMN`_time`; 
  

STR_TO_DATE的说明符的完整列表可以在 DATE_FORMAT ，这里是我使用的摘录：

 ％d每月的日期，数字（00..31）
％H小时（00..23）
％i分钟，数字（00..59）
％m月，数字（00..12）
％Y年份，数字，四位数
  

演示UPDATE

如果新列应具有属性NOT NOLL，则可以将操作之前的sql模式设置为'，然后重置sql_mode on：

  SET @old_mode = @@ sql_mode; 
 SET @@ sql_mode =''; - 在DATETIME列中允许零值
 
 ALTER TABLE`my_table` ADD COLUMN`date_time` DATETIME NOT NULL; 
更新`my_table` SET`date_time` = STR_TO_DATE（`_time`，'％Y％m％d％H％i'）; 
 ALTER TABLE`my_table` DROP COLUMN`_time`; 
 
 SET @@ sql_mode = @old_mode; 
  

更新演示

In my MySql table there's a column called _time of type varchar. The values it holds are in the format: year month day hour minute without the whitespaces: 201409201945 I want to convert it to datetime so I'm doing this:

ALTER TABLE `my_table` CHANGE COLUMN `_time` `date_time` DATETIME NOT NULL; 

And it throws this error for some reason:

Error Code: 1292. Incorrect datetime value: '201409201945' for column '_date_time' at row 1 0.036 sec

解决方案

The three steps @Arkain mentioned would be with the help of the function STR_TO_DATE

-- add the new column
ALTER TABLE `my_table` ADD COLUMN `date_time` DATETIME; 

-- update the new column with the help of the function STR_TO_DATE
UPDATE `my_table` SET `date_time` = STR_TO_DATE(`_time`, '%Y%m%d%H%i');

-- drop the old column
ALTER TABLE `my_table` DROP COLUMN `_time`;

The complete list of specifiers for STR_TO_DATE can be found at DATE_FORMAT, here an excerpt with those I used:

%d  Day of the month, numeric (00..31)
%H  Hour (00..23)
%i  Minutes, numeric (00..59)
%m  Month, numeric (00..12)
%Y  Year, numeric, four digits

Demo of the UPDATE

If the new column should have the attribute NOT NOLL, one way could be to set the sql mode before the operation to '' and reset the sql_mode later on:

SET @old_mode = @@sql_mode;
SET @@sql_mode = '';        -- permits zero values in DATETIME columns

ALTER TABLE `my_table` ADD COLUMN `date_time` DATETIME NOT NULL; 
UPDATE `my_table` SET `date_time` = STR_TO_DATE(`_time`, '%Y%m%d%H%i');
ALTER TABLE `my_table` DROP COLUMN `_time`;

SET @@sql_mode = @old_mode;

Updated Demo

这篇关于无法在MySql中将varchar转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！