Time Calculation- need either positive or negative times in (hh:mm:ss)

[WoodyGuthrie]

I have no problem getting "elapsed Time" like this:
CONVERT(Varchar,EndTime - StartTime ,108) As ElapsedTime


My problem is getting "Delay Time". This calculation can result in a positive hh:mm:ss but can also be negative (hh:mm:ss) as is the case when StartTime is earlier than the ScheduledTime. When I use the following and use DateDiff with the Hour parameter, I can get -3, for example, when the StartTime is 7:30:15AM and the ScheduledTime is 10:12:23AM. But how do I get hh:mm:ss? Obviously, I suppose NOT by using DateDiff. Can this be done without getting into the gastly stuffs, replaces,etc.?
----------------------------------
Case
When StartTime < ScheduledTime /*Negative Delay Time*/
Then Convert(Varchar, DateDiff(Hour,ScheduledTime, StartTime),108)

When StartTime >= ScheduledTime /*Positive Delay Time*/
Then Convert(Varchar,StartTime - ScheduledTime,108)

End as DelayTime
-------------------------------------


Thanks in advance for any help.

 

[ESquared]

It can't be done without getting into the "ghastly stuff."

This is a presentation issue, anyway. Why not perform the calculation in the front end?

By the way, DateDiff does NOT return elapsed time, and it is a bad way to try to calculate it. What it does is count the number of time-boundaries crossed. So the following two queries will give the same result, even though they are nearly 48 hours different in elapsed time:

SELECT DateDiff(dd, '2007-10-04 00:00', '2007-10-05 23:59:59')
SELECT DateDiff(dd, '2007-10-04 23:59:59', '2007-10-05 00:00')

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[WoodyGuthrie]

Thanks for the comment. I did handle it in the report field. Thanks again.