Calculate start time, set 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 cannot figure out how to subtract all the time instead of HH / MM / SS / MS separately.

Answer 1

Due to 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 it more briefly, of course, I just feel that CTE helps to express the steps.

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

Answer 2

You can get the part there:

SELECT *, DateAdd( millisecond, DateDiff( millisecond, ElapsedTime, 0 ), FinalDateTime )
  FROM @TABLE

, DATEADD DATEDIFF INT . , , .