20

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.

slavoo
  • 5,798
  • 64
  • 37
  • 39
Kevin Tianyu Xu
  • 646
  • 2
  • 8
  • 15

3 Answers3

24

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)
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • It's to calculate the interval between two scientific metrics. Store the interval value and then add it to another datetime2 to make predictions. Though the gap between two dates are not typically that big (4 days in the example above). Maybe this is slightly more realistic. select DATEDIFF(MICROSECOND, cast('20010101 23:45:21.12347' as datetime2), cast('20010102 00:05:15.56786' as datetime2)) – Kevin Tianyu Xu Oct 14 '13 at 04:40
  • It seem to work for small enough intervals. Maybe just be mindful that there is an upper limit for microseconds for that kind of precision? If only SQL server datetime2 behaved in a similar fashion compared to datetime in that regard, then I wouldn't have to go through such trouble. – Kevin Tianyu Xu Oct 14 '13 at 04:45
4

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

select DATEDIFF(millisecond,'20010105 12:35:15.56786','20010101 23:45:21.12347')
Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
-1

After 5 years, this is unlikely to help slavoo, but this long-winded example probably does what was asked for:

    @FromDateTime DATETIME2 = CAST('20010101 23:45:21.12347' AS DATETIME2),
    @ToDateTime DATETIME2 = CAST('20010105 12:35:15.56786' AS DATETIME2),
    @FromMicroSecs NUMERIC,
    @FromDateTimeNoMicroSecs DATETIME,
    @ToMicroSecs NUMERIC,
    @ToDateTimeNoMicroSecs DATETIME;

SELECT
    @FromMicroSecs = DATEPART(MICROSECOND, @FromDateTime),
    @FromDateTimeNoMicroSecs = CAST(DATEADD(MICROSECOND, -1 * @FromMicroSecs, @FromDateTime) AS DATETIME),
    @ToMicroSecs = DATEPART(MICROSECOND, @ToDateTime),
    @ToDateTimeNoMicroSecs = CAST(DATEADD(MICROSECOND, -1 * @ToMicroSecs, @ToDateTime) AS DATETIME);

SELECT
    CAST(DATEDIFF(SECOND, @FromDateTimeNoMicroSecs, @ToDateTimeNoMicroSecs) AS NUMERIC) * 1000000 + @ToMicroSecs - @FromMicroSecs AS AnswerInMicroseconds;```
articus
  • 1