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?

What do you mean by "wrong"? what is your expected correct output? For me it looks correct only — Kannan Kandasamy, Oct 13 '17 at 12:59
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. — AdamL, Oct 13 '17 at 13:02
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(, 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. — Richard Hansell, Oct 13 '17 at 13:08
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? — img.simone, Oct 16 '17 at 06:46

Stanislav Kundii · Accepted Answer · 2017-10-13T13:18:59.827

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')

edited Oct 13 '17 at 13:18

answered Oct 13 '17 at 13:00

Stanislav Kundii

2,874
1
11
17

1

doesn't work for any other hour value, try: `SELECT FORMAT(03.92*60,'00:00')` – Tanner Oct 13 '17 at 13:11
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 – agershun Feb 16 '23 at 08:55

Sean Lange · Answer 2 · 2017-10-13T14:32:29.590

1

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)

edited Oct 13 '17 at 14:32

answered Oct 13 '17 at 13:14

Sean Lange

33,028
3
25
40

Arithmetic overflow. Round/FLOOR the second part – Stanislav Kundii Oct 13 '17 at 13:42
Not work.. If you set @input = 0.83, the result will be 00:49 and I need that the result is 00:50 – img.simone Oct 16 '17 at 06:47

score 0 · Answer 3 · answered Aug 09 '18 at 14:15

0

You can Try this

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

answered Aug 09 '18 at 14:15

Sanket Parchande

894
9
14

codetuner · Answer 4 · 2020-09-20T18:04:40.307

0

To render 4.92 as '04:55':

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

edited Sep 20 '20 at 18:04

answered Sep 20 '20 at 16:34

codetuner

316
3
8

score -1 · Answer 5 · edited Nov 11 '20 at 12:39

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$

Convert time float to format HH:mm sql server

5 Answers5

Linked