字符串类型的时间日期相见 转 datetime

[aCracker]

我想将startdate +starttime 存入对应的updateTime ,而且startdate 需要改一下格式，如20160310改为2016-03-10
startdate ，starttime 是varchar 类型。

如下表，
mysql> select * from test_time;
+----+-----------+-----------+------------+
| ID | startdate | starttime | updateTime |
+----+-----------+-----------+------------+
| 1 | 20160310 | 09:10:22 | NULL |
| 2 | 20160311 | 22:23:31 | NULL |
+----+-----------+-----------+------------+
处理后的结果：
mysql> select * from test_time;
+----+-----------+-----------+------------+
| ID | startdate | starttime | updateTime |
+----+-----------+-----------+------------+
| 1 | 20160310 | 09:10:22 | 2016-03-10 09:10:22 |
| 2 | 20160311 | 22:23:31 | 2016-03-11 22:23:31 |
+----+-----------+-----------+------------+

这sql应该怎么写，才能实现呢？

CREATE TABLE `test_time` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `startdate` varchar(20) DEFAULT NULL,
  `starttime` varchar(20) DEFAULT NULL,
  `updateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_time
-- ----------------------------
INSERT INTO `test_time` VALUES ('1', '20160310', '09:10:22', null);
INSERT INTO `test_time` VALUES ('2', '20160311', '22:23:31', null);

[aCracker]

引用 1 楼 yupeigu 的回复:

试试这个：

mysql> select cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) from test_time;
+------------------------------------------------------------------------------------------+
| cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) |
+------------------------------------------------------------------------------------------+
| 2016-03-10 09:10:22                                                                      |
| 2016-03-11 22:23:31                                                                      |
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

怎么插入到对应的位置呢？ 根据ID？

[LongRui888]

引用 2 楼 aCracker 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 试试这个：

mysql> select cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) from test_time;
+------------------------------------------------------------------------------------------+
| cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) |
+------------------------------------------------------------------------------------------+
| 2016-03-10 09:10:22                                                                      |
| 2016-03-11 22:23:31                                                                      |
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

怎么插入到对应的位置呢？ 根据ID？[/quote] 如果数据已经有了，你要更新字段，可以直接： update test_time set updateTime= cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime)

[LongRui888]

试试这个：

mysql> select cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) from test_time;
+------------------------------------------------------------------------------------------+
| cast(concat(date_format(cast(startdate as date),'%Y-%m-%d'),' ', starttime) as datetime) |
+------------------------------------------------------------------------------------------+
| 2016-03-10 09:10:22                                                                      |
| 2016-03-11 22:23:31                                                                      |
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[ACMAIN_CHM]

update test_time set updateTime=STR_TO_DATE(concat(startdate,starttime),'%Y%M%d%h:%i:%s');