将时间浮点转换为HH:MM SQL Server格式 [英] Convert time float to format HH:mm sql server

问题描述

我需要将浮点十进制数字格式化为时间格式:小时:分钟.

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

我用输入浮点数和输出varchar(6)编写了这个标量值函数:

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

从结果中可以看到，错误转换有3种:0.33 = 00:19 //0.58 = 00:34 //0.83 = 00:49 .

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

如何设置正确的输出?

推荐答案

使用函数FORMAT SQL 2012+ https://docs.microsoft.com/zh-cn/sql/t-sql/functions/format-transact-sql

Use function FORMAT SQL 2012+ https://docs.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')

这篇关于将时间浮点转换为HH:MM SQL Server格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！