SQL - Operand data type datetime2 is invalid for subtract operator

Question

I want to subtract the value of the first row from the value of the second row of column _timestamp (shown below). _number is the ordering column in my data.

and put the result in a new column called diff. I have tried it with the following query

use dbtest

select 
    t2._number, t2._timestamp, 
    coalesce(t2._timestamp - (select t1._timestamp from dbo.tcp t1 
                              where t1._number = t2._number + 1), t2._timestamp) as diff
from 
    dbo.tbl t2

but I am getting the following error.

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

Any tips would be appreciated. I have a huge record and I want to automatically apply it for the entire column.I am using SQL Sever 2008.

Answer 1

As mentioned in the comments, you can't subtract timestamps with - operator. Use DATEDIFF function instead. To get the difference of the current row and the next row's timestamps, use OUTER APPLY.

select t2._number,t2._timestamp, 
datediff(microsecond,t2._timestamp,t1._timestamp) as diff
from dbo.tbl t2
outer apply (select t1._timestamp 
             from dbo.tcp t1
             where t1._number = t2._number + 1) t1

Edit: To update a column named diff per the OP's comment,

with cte as (          
select t2._number,t2._timestamp, t2.diff,
datediff(microsecond,t2._timestamp,t1._timestamp) as diff_col
from t t2
outer apply (select t1._timestamp 
             from t t1
             where t1._number = t2._number + 1) t1
   )
update cte set diff=diff_col;