Subtracting time / timestamp

Database
Junior Supporter

Subtracting time / timestamp

My query:

sel lastaltertimestamp,createtimestamp, 

--(createtimestamp- lastaltertimestamp) hour to second

cast ( substring ( cast(createtimestamp as varchar(20)) , 12,19) as time(0)) as cts,

cast ( substring ( cast(lastaltertimestamp as varchar(20)) , 12,19) as time(0)) as latts,

(cts-latts) hour to second

from tduser.TVFields 

1. How do i store the o/p of (createtimestamp- lastaltertimestamp) hour to second into a variable in a table. And i want to do some operation like avg() etc on that field later on

2. I tried to convert it into time(0) and subtract the value and then i could store it into a variable time in the table. However, (cts-latts) hour to second doesn't work - invalid operation on datetime or interval value error i get.

Just (cts-latts) works though, but i couldn't understabd the o/p






LastAlterTimeStamp CreateTimeStamp cts latts (cts-latts)
31-08-2009 22:52 31-08-2009 23:02 23:02:24 22:52:24 5,000
31-08-2009 22:52 31-08-2009 23:02 23:02:24 22:52:24 5,000
31-08-2009 22:52 31-08-2009 23:02 23:02:24 22:52:24 5,000
31-08-2009 22:52 31-08-2009 23:02 23:02:24 22:52:24 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000
31-08-2009 22:53 31-08-2009 23:03 23:03:31 22:53:31 5,000