Query for Difference between two Fields

Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??Looks to me like the difference is 9:627 !
;)

When you say you need to find the difference, are you talking about determining that they ARE different, or finding the elapsed time?

If it's the latter, take a look into DATEDIFF in BOL...

Also, it would help if you tell us what type of field you have there...as that would also effect the answer.|||I need to find the the difference between two times.

Data Types - Field1 and Field2 are both nvarchar|||Look up DATEDIFF in BOL for starters...

DATEDIFF(ms, Field1, Field2)|||DECLARE @.foo NVARCHAR(50)
, @.bar NVARCHAR(50)

SET @.foo = N'16:12:27:123'
SET @.bar = N'16:12:36:750'

IF 0 = IsDate(@.foo)
PRINT 'foo is not a date'
IF 0 = Isdate(@.bar)
PRINT 'bar is not a date'
ELSE
SELECT DateDiff(ms, @.foo, @.bar)-PatP|||http://weblogs.sqlteam.com/jeffs/archive/2007/07/03/60248.aspx
Well worth a read.sql