SQL Server 2008中分钟数超过60的小时和分钟总和 [英] Sum of hour and minutes when minutes exceeding 60 in SQL Server 2008

问题描述

亲爱的先生，

我在下面写了一个SQL查询。我输入为6.78。我希望将输出显示为7.18（表示7小时18分钟）。以下查询给出了错误的答案（6.96）

Dear Sir,
I have written one SQL Queries in below. i given input as 6.78. i want to display output as 7.18(means 7 hours and 18 mins). This below Queries is giving wrong answer(6.96)

declare @eff table (id int identity(1,1), data decimal(18,2))
--insert into  @eff('6.78')

insert @eff(data) values (6.78)  --,(15.70)
select * from @eff

declare @eff2 table (id int identity(1,1),data decimal(18,2))
declare @temp2 decimal (18,2)

declare @i int
set @i=1
while((select count (data) from @eff) >=@i)
begin

if((select PARSENAME(data,1) from @eff where id = @i ) > 59 )

begin
set @temp2 = ((select data - (0.60)+convert(decimal(18,2),'.'+(convert(varchar(20),PARSENAME( data,1)))) from @eff where id = @i))
print @temp2
insert @eff2 (data) values (@temp2)
print @temp2
end


set @i=@i+1
end
select * from @eff2

推荐答案

如果你想在几小时和几分钟内工作，那么先转换它到总分钟（十进制值的整数部分乘以60再加上小数部分乘以100）然后再次返回：小数的整数部分是整数分数除以60，小数部分是模数60的值转换为小数，除以100.

If you want to work in hours and minutes, then first convert it to a total minutes (integer part of the decimal value times 60 plus fractional part times 100) and then work it back again: the integer part of the decimal is the integer number of minutes divided by 60, the fractional part is the modulus 60 value converted to a decimal, and divided by 100.

这篇关于SQL Server 2008中分钟数超过60的小时和分钟总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！