Calculate start time given end time (datetime2) and duration (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

I want to subtract time(7) from Datetime2(7). I can't figure out how to subtract the whole time instead of HH/MM/SS/MS separately.

Answer 1

Because of the granularity, you need to perform the nanosecond calculation separately. So, subtract the elapsed time in whole seconds, then subtract the nanosecond part.

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;

You can do this more concisely, of course, I just feel like the CTE helps express the steps.

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