SQL: float number to hours format

Question

Is there a easy way to format a float number in hours in Ms SQL server 2008?

Examples:

• 1.5 -> 01:30
• 9.8 -> 09:48
• 35.25 -> 35:15

Thanks a lot.

Answer 1

I like this question!

DECLARE @input float = 1.5;

DECLARE @hour int = FLOOR(@input);
DECLARE @minutes int = (SELECT (@input - FLOOR(@input)) * 60);

SELECT RIGHT('00' + CONVERT(varchar(2), @hour), 2) + ':' + RIGHT('00' + CONVERT(varchar(2), @minutes), 2);

Answer 2

SELECT SUBSTRING(CONVERT(NVARCHAR, DATEADD(MINUTE, 1.5*60, ''), 108), 1, 5)

This works by:

• starting from the "zero" date

• adding 1.5 x 60 minutes (i.e. 1.5 hours)

• formatting the result as a time, hh:mm:ss (i.e. format "108")

• trimming off the seconds part

It is necessary to use 1.5 x 60 minutes instead of 1.5 hours as the DATEADD function truncates the offset to the nearest integer. If you want high-resolution offsets, you can use SECOND instead, suitable scaled (e.g. hours * 60 * 60).