>I've got a list of times and I want to cast them as a time data type but I get an error because they are not a consistent format.
>
>example
>
>20:30
>22:00
>3
>4
>
>so cast('20:30' as time) works but cast('3' as time fails
>
>This is a data import . Any ideas on how I can make my SQL accept and cast the different time string .
>
>Thanks
>
>Nick

what are 3 and 4?
hours, minutes?

DECLARE @Test TABLE (Test varchar(5))
INSERT INTO @Test 
SELECT '20:30'
UNION
SELECT '22:00'
UNION
SELECT '3'
UNION
SELECT '4'


SELECT CAST(CASE WHEN CHARINDEX(':', Test) = 0
                 then Test+':00'
            ELSE Test END as time) FROM @Test