I have to write an SQL view that returns the time part of a datetime column as a string in the format hhmmss (apparently SAP BW doesn't understand hh:mm:ss).

This code is the SAP recommended way to do this, but I think there must be a better, more elegant way to accomplish this

TIME = case len(convert(varchar(2), datepart(hh, timecolumn)))
             when 1 then       /* Hour Part of TIMES */
               case convert(varchar(2), datepart(hh, timecolumn))
                 when '0' then '24'    /* Map 00 to 24 ( TIMES ) */
                 else '0' + convert(varchar(1), datepart(hh, timecolumn))
               end
            else convert(varchar(2), datepart(hh, timecolumn))
            end
         + case len(convert(varchar(2), datepart(mi, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(mi, timecolumn))
              else convert(varchar(2), datepart(mi, timecolumn))
           end
         + case len(convert(varchar(2), datepart(ss, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(ss, timecolumn))
              else convert(varchar(2), datepart(ss, timecolumn))
           end

This accomplishes the desired result, 21:10:45 is displayed as 211045.

I'd love for something more compact and easily readable but so far I've come up with nothing that works.