Eirikur Eiriksson (1/16/2014)

---

Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint.

SELECT

StartDT

,EndDT

,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT))

,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks

,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks

,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY

,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmX

FROM #JBMTest;

Msg 8115, Level 16, State 2, Line 15

Arithmetic overflow error converting expression to data type datetime.