... CASE WHEN ISNUMERIC(LEFT(UpTime1,2)) = 0 OR CAST(LEFT(UpTime1,2) AS int) NOT BETWEEN 00 AND 23 THEN 0 WHEN ISNUMERIC(RIGHT(UpTime1,2)) = 0 OR CAST(RIGHT(UpTime1,2) AS int) NOT BETWEEN 00 AND 59 THEN 0 WHEN SUBSTRING(UpTime1,3,1) <> ':' THEN 0 WHEN ISNUMERIC(LEFT(DownTime1,2)) = 0 OR CAST(LEFT(DownTime1,2) AS int) NOT BETWEEN 00 AND 23 THEN 0 WHEN ISNUMERIC(RIGHT(DownTime1,2)) = 0 OR CAST(RIGHT(DownTime1,2) AS int) NOT BETWEEN 00 AND 59 THEN 0 WHEN SUBSTRING(DownTime1,3,1) <> ':' THEN 0 ELSE DATEDIFF( minute, CAST(UpTime1 AS datetime), CAST(DownTime1 AS datetime)) END AS TotalTimeUp1>This stored procedure works great as long as the values in the UpTime1 and DownTime1 are in a valid time format HH:MM. If the're not, it fails to return anything. The DateDiff statement is calculating the number of minutes and this is the line that gives me a hard time if there is any bad data. Those fields are a varchar(5).
>SELECT *,datepart(hh,RecDateTime)*60+datepart(mi,RecDateTime) as iStartTime,convert(varchar(10),RecDateTime,110) as justdate, > >DATEDIFF( minute, CAST(UpTime1 AS datetime), CAST(DownTime1 AS datetime)) as TotalTimeUp1 > >from dbo.co_Pat_Tourniquet (nolock) >WHERE meetingnumber=@meetingnumber and noteID=@noteID and isactive=1 > ORDER BY recDateTime Desc >>