>I am trying to figure out how to code a stored procedure to return elapsed time values. The date portion is stored in the RecDateTime, the fields UpTime1 and DownTime1 have string values such as 15:30 and 15:45. The goal is turn return an additional column show the difference between UpTime1 and DownTime1, in this case it would be 15 minutes.
>
>A hitch in the plan is that if there is not a value in UpTime1 and/or DownTime1 then I want to return a zero.
>
>
>RecDateTime UpTime1 DownTime1
>01/09/2009 12:13:00 15:30 15:45
>
>
>RecDateTime is a datetime value
>UpTime1 and DownTime1 are varchr(5)
>
>Thanks for any help.
>
>Kirk
Something like
select (CAST(LEFT(DownTime1,2) as int) - case(LEFT(UpTime1,2) as int) * 60) as Hour_Difference,
CAST(RIGHT(DownTime1,2) as int) as UpTime1Min, case(RIGHT(UpTime1,2) as int) as DownTime1Min
IF DownTimeMin lees than UpTown1 we need to substract the difference from 60 and decrease the total time by this difference.
Sounds a bit tricky, needs more thinking...
Needs tweaking, but this is just an idea.
Of course you need cases, etc.
If it's not broken, fix it until it is.
My Blog