[SOLVED] Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?

Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?

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.

Solution

you could use a user-defined function like:

create FUNCTION [dbo].[udfStrippedTime]
(
    @dt datetime
)
RETURNS varchar(32)
AS
BEGIN
    declare @t varchar(32)
    set @t = convert( varchar(32), @dt, 108 )
    set @t = left(@t,2) + substring(@t,4,2)

    RETURN @t
END

then

select dbo.udfStrippedTime(GETDATE())

the logic for the seconds is left as an exercise for the reader