How do I calculate interval between two datetime2 columns (SQL Server)?

Question

Hi I'm trying to calculate the difference between two columns of datetime2 type.

However SQL server (2012) doesn't seem to like the following:

select cast ('2001-01-05 12:35:15.56786' as datetime2)
    - cast ('2001-01-01 23:45:21.12347' as datetime2);

Msg 8117, Level 16, State 1, Line 2
Operand data type datetime2 is invalid for subtract operator.

Now it works if I cast it to a datetime type:

select cast (cast ('2001-01-05 12:35:15.56786' as datetime2) as datetime) 
    - cast (cast ('2001-01-01 23:45:21.12348' as datetime2) as datetime);

1900-01-04 12:49:54.443

However, I am losing precision when I cast it to datetime (note the 3 decimal precision above). In this case, I actually need all 5 decimal points. Is there a way to get the interval between two datetime2 columns and still maintain 5 decimal points of precision? Thanks.

Answer 1

You can simply use DateDiff

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

select DATEDIFF(MILLISECOND, cast('20010101 23:45:21.12347' as datetime2), 
                             cast('20010105 12:35:15.56786' as datetime2))

Unfortunately, attempting to get the precision you require with this:

select DATEDIFF(MICROSECOND, cast('20010101 23:45:21.12347' as datetime2), 
                             cast('20010105 12:35:15.56786' as datetime2)) 

results in an overflow error:

The datediff function resulted in an overflow. 
The number of dateparts separating two date/time instances is too large. 
Try to use datediff with a less precise datepart.

One way to achieve the precision you want would be to iteratively break into the granular time components (days, hours, minutes, seconds, etc.) and subtract this from the values using DateAdd(), e.g.

remainingAtLowerGranularity = DateAdd(granularity, -1 * numFoundInStep, value)