这很简单,只需使用简单的算术运算:
DECLARE @TABLE TABLE (Start DATETIME, Finish DATETIME, Duration TIME);
INSERT INTO @TABLE VALUES ('02/28/2017 08:00','02/28/2017 08:30','');
INSERT INTO @TABLE VALUES ('02/28/2017 09:00','02/28/2017 09:40','');
INSERT INTO @TABLE VALUES ('02/28/2017 10:02','02/28/2017 11:53','');
INSERT INTO @TABLE VALUES ('02/28/2017 11:56','02/28/2017 12:45','');
INSERT INTO @TABLE VALUES ('02/28/2017 13:45','02/28/2017 23:59','');
UPDATE @TABLE
SET Duration = Finish - Start;
SELECT * FROM @TABLE;
返回:
Start Finish Duration
---------------------------------------------------------
28/02/2017 08:00:00 28/02/2017 08:30:00 00:30:00
28/02/2017 09:00:00 28/02/2017 09:40:00 00:40:00
28/02/2017 10:02:00 28/02/2017 11:53:00 01:51:00
28/02/2017 11:56:00 28/02/2017 12:45:00 00:49:00
28/02/2017 13:45:00 28/02/2017 23:59:00 10:14:00
这里唯一需要注意的是,它们需要在同一天。你明确说明了持续时间永远不会超过一天,所以应该没问题。
如果您想将结果添加到
Duration
的原始值中,则只需将其加上即可...
INSERT INTO @TABLE VALUES ('02/27/2017 08:00','02/28/2017 08:30','00:15');
INSERT INTO @TABLE VALUES ('02/28/2017 09:00','02/28/2017 09:40','00:14');
INSERT INTO @TABLE VALUES ('02/28/2017 10:02','02/28/2017 11:53','00:13');
INSERT INTO @TABLE VALUES ('02/28/2017 11:56','02/28/2017 12:45','02:16');
INSERT INTO @TABLE VALUES ('02/28/2017 13:45','02/28/2017 23:59','00:17');
UPDATE @TABLE
SET Duration = Duration + (Finish - Start);
返回:
Start Finish Duration
---------------------------------------------------------
27/02/2017 08:00:00 28/02/2017 08:30:00 00:45:00
28/02/2017 09:00:00 28/02/2017 09:40:00 00:54:00
28/02/2017 10:02:00 28/02/2017 11:53:00 02:04:00
28/02/2017 11:56:00 28/02/2017 12:45:00 03:05:00
28/02/2017 13:45:00 28/02/2017 23:59:00 10:31:00