sql - 计算给定结束时间 (datetime2) 和持续时间 (time(7)) 的开始时间

Question

DECLARE @TABLE TABLE (ID INT IDENTITY(1,1), FinalDateTime Datetime2(7), 
 ElapsedTime Time(7))

INSERT INTO @TABLE (FinalDateTime, ElapsedTime)
SELECT '2014-01-21 00:00:00.1110010','12:00:00.1100009' 
  -- Expected Output = '2014-01-20 12:00:00.0010001'
UNION ALL
SELECT '2014-01-20 14:00:00.1110010','02:00:00.1100009' 
  -- Expected Output = '2014-01-20 12:00:00.0010001'
UNION ALL
SELECT '2014-01-20 12:02:00.1110010','00:02:00.1100009' 
  -- Expected Output = '2014-01-20 12:00:00.0010001'
UNION ALL
SELECT '2014-01-20 12:00:02.1110010','00:00:02.1100009' 
  -- Expected Output = '2014-01-20 12:00:00.0010001'
UNION ALL
SELECT '2014-01-20 12:00:00.1110010','00:00:00.1100009' 
  -- Expected Output = '2014-01-20 12:00:00.0010001'

SELECT * FROM @TABLE

我想从 Datetime2(7) 中减去 time(7)。我不知道如何减去整个时间，而不是分别减去 HH/MM/SS/MS。

Answer 1

由于粒度的原因，需要单独进行纳秒计算。因此，减去以整秒为单位的耗时，然后减去纳秒部分。

DECLARE @TABLE TABLE (FinalDateTime Datetime2(7), ElapsedTime Time(7));

INSERT INTO @TABLE (FinalDateTime, ElapsedTime) VALUES
('2014-01-21 00:00:00.1110010','12:00:00.1100009'),
('2014-01-20 14:00:00.1110010','02:00:00.1100009'),
('2014-01-20 12:02:00.1110010','00:02:00.1100009'),
('2014-01-20 12:00:02.1110010','00:00:02.1100009'),
('2014-01-20 12:00:00.1110010','00:00:00.1100009');

;WITH x AS 
(
  SELECT 
    FinalDateTime, 
    ElapsedTime,
    ElapsedSeconds = DATEDIFF(SECOND, '0:00', ElapsedTime),
    AdditionalNanoseconds = DATEPART(NANOSECOND, ElapsedTime)
  FROM @TABLE
)
SELECT 
  FinalDateTime, 
  ElapsedTime, 
  StartTime = DATEADD(NANOSECOND, -AdditionalNanoseconds, 
    DATEADD(SECOND, -ElapsedSeconds, FinalDateTime)
  ) 
FROM x;

当然，你可以做得更简洁，我只是觉得CTE有助于表达步骤。

SELECT 
  FinalDateTime, 
  ElapsedTime, 
  StartTime = DATEADD(NANOSECOND, -(DATEPART(NANOSECOND, ElapsedTime)), 
    DATEADD(SECOND, -(DATEDIFF(SECOND, '0:00', ElapsedTime)), FinalDateTime)
  ) 
FROM @TABLE;