无法在MySql中将varchar转换为datetime [英] Unable to convert varchar to datetime in MySql
问题描述
年月日小时分钟
没有空格: 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年份,数字,四位数
如果新列应具有属性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
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;
这篇关于无法在MySql中将varchar转换为datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!