T-SQL Convert int to HH:MM

Question

How simply (without variables) convert integer (for example 8) to format HH:MM = 08:00 in SQL Server? Or 2,75 = 02:45?

It's not a good idea to store time as a float like this. Why not use `time` instead? Using a floating point type like this almost guarantees rounding issues — Panagiotis Kanavos, May 24 '21 at 11:25
Your question is a little confusing, you state you want to convert your `integer` value, but `2.75` *isn't* an `int`; are you sure your column isn't a `decimal`? If you did try to store the value `2.75` in an `int` column, it would end up being stored as `2`. — Thom A, May 24 '21 at 11:26
Other applications, even other developers on the same project, will have a hard time using such a custom time value. You gain no portability by using a number instead of `time` either. Most databases support time-of-day one way or another. No database used numbers instead of time — Panagiotis Kanavos, May 24 '21 at 11:28

score 1 · Answer 1 · answered May 24 '21 at 11:18

You can convert this to a time after using dateadd():

select convert(time, dateadd(second, 2.75 * 60 * 60, 0))

If you want a string, you can use string functions instead:

select concat(format(floor(val), '00'), ':', format((val % 1) * 60, '00'))
from (values (2.75)) v(val)

1 Answers1