Convert time float to format HH:mm sql server

Question

I need to format a float decimal number into a time format hour:minute.

I wrote this Scalar-Value Functions with an input float and output varchar(6):

CREATE FUNCTIONE formatOre ( @input float )
returns varchar(6) 
as
begin
declare @n float;
declare @hour int = floor(@input);
declare @minutes int = (select (@input - floor(@input)) * 60);
declare @val varchar(6)

set @val = right('00' + convert(varchar(2), @hour), 2) + ':' + right('00' + convert(varchar(2), @minutes), 2);

return @val

end

It looks like great, but not for everything records. This is my output:

select formatOre (0)    ---> 00:00
select formatOre (0.17) ---> 00:10
select formatOre (0.25) ---> 00:15
select formatOre (0.33) ---> 00:19
select formatOre (0.42) ---> 00:25
select formatOre (0.5)  ---> 00:30
select formatOre (0.58) ---> 00:34
select formatOre (0.67) ---> 00:40
select formatOre (0.75) ---> 00:45
select formatOre (0.83) ---> 00:49
select formatOre (0.92) ---> 00:55

As you can see from the results, there are 3 wrongs conversion: 0.33 = 00:19 // 0.58 = 00:34 // 0.83 = 00:49.

How do I set the correct output?

Slowpoke

What do you mean by "wrong"? what is your expected correct output? For me it looks correct only – Pollerd

why are you storing time in floating point or decimal ? – Rutharuthann

for input=.17 you get select (@input - floor(@input))*60 = 0.17*60 = 10.2 and since it's declared as int you got 10 as a result. – Destructor

I guess you are complaining because you want 0.33 to be 00:20, 0.58 to be 00:35 and 0.83 = 00:50. If you really are keen on using your clunky function to get this instead of a simple FORMAT command, then you could wrap the minutes calculation with a ROUND(<calc>, 0) ,i.e. "declare @minutes int = ROUND((select (@input - floor(@input)) * 60), 0);". This will give you the desired result, but there are far better ways to do this. – Neolamarckism

As Richard said, I wanto that 0.33 to be 00:20 and others. You wrote that there are "far better ways". So, which are these methods? – Slowpoke

Answer 1

Use function FORMAT SQL 2012+ https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

DECLARE  @input float = 4.92    
SELECT FORMAT(FLOOR(@input)*100 + (@input-FLOOR(@input))*60,'00:00')

Beetle

doesn't work for any other hour value, try: SELECT FORMAT(03.92*60,'00:00') – Kazoo

In my case I had to add conversion to number with 2 decimal points: SELECT FORMAT(FLOOR(@input)*100 + (@input*1.00-FLOOR(@input)*1.00)*60,'00:00') Without it the system looses minutes sometimes – Kironde

Answer 2

Not a fix to the problem but perhaps something you can utilize. Scalar functions are horrible for performance. But inline table valued functions are not. The function you posted can be very easily converted to an inline table valued function. Keep in mind that these must be ONLY a single select statement. If you have variables and such it become a multi-statement table valued function and the performance would be even worse than a scalar function.

Here is how you could convert that scalar into an inline table valued function.

CREATE FUNCTION formatOre 
(
    @input float 
)
returns table as return

select right('00' + convert(varchar(2), floor(@input)), 2) + ':' + right('00' + convert(varchar(2), floor((@input - floor(@input)) * 60)), 2)

Plantaineater

Arithmetic overflow. Round/FLOOR the second part – Beetle

Not work.. If you set @input = 0.83, the result will be 00:49 and I need that the result is 00:50 – Slowpoke

Answer 3

You can Try this

 SELECT FORMAT(FLOOR(ColumnNAme) + (ColumnNAme -FLOOR(ColumnNAme)),'00.00') from TableName

Oneida

Answer 4

To render 4.92 as '04:55':

SELECT FORMAT(DATEADD(minute, 4.92 * 60, '2000-01-01'), 'HH:mm')

Design

Answer 5

INSERT INTO t_att_inout(MM  ,YYYY   ,   DATE,EmpCode    ,Type   ,InTime,    OutTime)
select 10 as MM,2020 as YYYY, CONVERT(DATE,LEFT([Att Date],2)+'-OCT-2020',106),
emp_code,'fboth'as type,  
CONVERT(TIME(7),CONVERT(VARCHAR,FLOOR([in time]))+':'+RIGHT('00'+CONVERT(VARCHAR,CONVERT(INT,([In Time]-FLOOR([In Time]))*100)),2)),  
CONVERT(TIME(7),CONVERT(VARCHAR,FLOOR([out time]))+':'+RIGHT('00'+CONVERT(VARCHAR,CONVERT(INT,([Out time]-FLOOR([Out time]))*100)),2)) --,[IN time],[out time]
from CGAttend10$    

Pelota