Convert Military time to string representation

Question

I have an column declarated as int (called HourMil) wich store the time in military format. i need convert this values to an formated string (HH:MM)

example

HourMil  = 710 -> must be 07:10
HourMil  = 1305 -> must be 13:05

Actually i am using this code (and works ok) for convert the column HourMil to the string representation.

SELECT SUBSTRING(LEFT('0',4-LEN(CAST(HourMil AS VARCHAR)))+CAST(HourMil AS VARCHAR),1,2)+':'+SUBSTRING(LEFT('0',4-LEN(CAST(HourMil AS VARCHAR)))+CAST(HourMil AS VARCHAR),3,2) FROM MYTABLE

but I think this code can be improved.

Answer 1

Equations:

H = HourMil / 100;
M = HourMil - ( HourMil / 100 ) * 100;

Examples of working:

H = 710 / 100 = 7
M = 710 - (710 / 100) * 100 = 10

H = 1305 / 100 = 13
M = 1305 - (1305 / 100) * 100 = 5

Note: All /* operators MUST working under decimal numbers only. (integers)

Then you SQL will be:

CONCAT(CAST(H AS VARCHAR(8)), ':', CAST(M AS VARCHAR(8)));

With equations final seems to be:

CONCAT(CAST((HourMil / 100) AS VARCHAR(8)), ':', CAST((HourMil - ( HourMil / 100 ) * 100) AS VARCHAR(8)));

Answer 2

By creating a date, then formatting it:

SELECT SUBSTRING(CONVERT(nvarchar, DATEADD
   (hh, HourMil / 100, DATEADD(mi, HourMil % 100, '1900-01-01')), 8), 0, 6)

Answer 3

This is another method and also a shortest one

select stuff(right('0'+ltrim(hourmail),4),3,0,':') from
(
select 710 as hourmail union all
select 1305
) as t