[RESOLVED] Case Statement Datatype Problem

[gigemboy]

I am wanting to do a T-SQL case statement on an integer field, but output an actual string result. When trying to do so, it is giving a conversion error because it is assuming the datatype should be integer since the case statement is evaluating the integer field, yet the output is a string. Is there a way to write the case statement where it can do this?

The case statement below takes an integer that represents 24-hr time, and tries to output a string representation.. 1 equals "00:01", 1200 = "12:00", 1900 = "19:00" etc...

case
when [Start time] > 0 and [Start Time] < 10
     then '00:0' + convert(varchar(1),[Start Time])
when [Start time] > 9 and [Start Time] < 60
	 then '00:' + convert(varchar(2),[Start Time])
when [Start time] > 99 and [Start Time] < 960
	 then '0' + substring(convert(varchar(4),[Start Time]),1,1) + ':' + substring(convert(varchar(4),[Start Time]),2,2)
when [Start time] > 999 and [Start Time] < 2360 
	 then substring(convert(varchar(4),[Start Time]), 1,2) + ':' + substring(convert(varchar(4),[Start Time]), 3,2)
end as 'StartTime'

*** Problem was because the case statement wasn't fully finished, I filled in the other case scenarios for the post and didn't test it until afterwards... after posting, it did in fact work