SQL中从小数到HH:MM格式的转换 [英] Dicimal to HH:MM Format Conversion in SQL

问题描述

我有一张桌子，我想将持续时间列的所有行(十进制格式)更新为HH:MM格式.

ECode	持续时间
101	101.75
101	69.56
102	54.97
104	35.32

输出应该是这样的:-

ECode	持续时间
101	102.15
101	69.56
102	55.37
104	35.32

我们正在计算小数点后的时间，如果小数点后60或更多，则将有60，那么我们在小数点前加1，然后从60减去后剩下的就照原样显示了.示例101.75，这里的小数点后75是第一个我们检查它是gtreater然后等于60.如果是，则减去60并在小数点前加1，然后将其余的15放在小数点后，结果变为102.15.
如果可能的话，请共享该查询，以便我将更新表的所有行并获取所需的结果.

I Have a table and i want to update all the rows of Duration Column which is in Decimal format to HH:MM Format.

ECode	Duration
101	101.75
101	69.56
102	54.97
104	35.32

the out put should be like this:-

ECode	Duration
101	102.15
101	69.56
102	55.37
104	35.32

We are calculating the time after the decimal and if after decimal 60 or more then 60 will be there we are adding 1 before decimal and remaining after subtraction from 60 we are showing as it is.Example 101.75 , here after decimal 75 is there so firstly we check it is gtreater then and equal to 60 .if yes then subtract 60 and add 1 before decimal and put the remaining 15 after decimal.so the result become 102.15.
Please share the query if possible so that i will update all rows of the table and get the desired result.

推荐答案

update Table set duration = duration + 0.4 where duration - floor(duration) >= 0.6

该查询解决了我的问题.

This Query had solved my problem.

这篇关于SQL中从小数到HH:MM格式的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！