How to calculate the spacing between two datetime2 columns (SQL Server)?

Question

Hey. I am trying to calculate the difference between two columns of type datetime2.

However, SQL Server (2012) does not look like this:

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 passed it to the 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 lose accuracy when I throw it into datetime (note the 3 decimal precision above). In this case, I really need all 5 decimal points. Is there a way to get the spacing between two datetime2 columns and save 5 decimal precision points? Thanks.

Answer 1

You can just use DateDiff

Returns the counter (signed integer) of the specified datepart boundaries that intersect between the specified start and end values.

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

Unfortunately, trying to get the accuracy you need:

 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 required accuracy would be an iterative gap in the drilldown components (days, hours, minutes, seconds, etc.) and subtract this from the values ​​using DateAdd (), for example.

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

Answer 2

To find the difference between two dates, you need to use the DATEDIFF function

 select DATEDIFF(millisecond,'20010105 12:35:15.56786','20010101 23:45:21.12347')