Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?
- by Johann J.
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.
