produce leading 0 in the minutes field

Question

This snippet:

select Datename(hh,DATEADD(HH, -5, [time])) + ':' + Datename(mi,[time])....

will produce:

11:4

But i need the leading '0' in front of the '4'.

Answer 1

You can pad the minutes with leading zeros, and then take the right two characters:

SELECT Datename(hh,DATEADD(HH, -5, [time])) + ':' + 
    right('00' + Datename(mi,[time]), 2)