将十进制时间转换为小时和分钟 [英] Convert decimal time to hours and minutes

问题描述

一直在努力解决这个问题，似乎找不到正确的答案，虽然有很多提到转换，但没有任何具体的工作.

Been struggling with this and can't seem to find the right answer, although there are plenty of mentions for converting, but nothing specific is working.

我需要将数据类型为 float 的时间转换为小时和分钟.所以 13.50 为 13.30.数据类型与 DB 中的 float 一样固定，因此无法更改.数据库是 SQL Server 2008R2

I need to convert a time with data type of float into hours and minutes. So 13.50 as 13.30. The data type as fixed as float in DB so cannot change. DB is SQL Server 2008R2

尝试过:

cast(cast(floor(fdsViewTimesheet.perStandardHours) as    
float(2))+':'+cast(floor(100*(    
fdsViewTimesheet.perStandardHours - floor(fdsViewTimesheet.perStandardHours)))as 
float(2)) as time) AS STANDARD_HOURS

但我收到错误消息 不允许从实时数据类型显式转换为时间" 尝试过 as char 而不是 as float但查询挂起.

But I get error message "Explicit conversion from data type real to time is not allowed" Have tried as char instead of as float but query hangs.

我做错了什么?我只是想将一个浮点值转换成小时和分钟.如果有人能指出我正确的方向，我将不胜感激.

What am I doing wrong? I just want to convert a float value into hours and minutes. Would be grateful if someone could point me in the right direction.

推荐答案

你可以试试:

DECLARE @HOURS decimal(7,4) = 20.5599
SELECT  CAST(CONVERT(VARCHAR,DATEADD(SECOND, @HOURS * 3600, 0),108) AS TIME)

输出:20:33:35

output : 20:33:35

但请记住:在 MSSQL 中只输入 24 小时以下的时间

But remember : Type Time in MSSQL only under 24hrs

如果您想要超过 24 小时，请尝试:

If you want greater than 24hrs, try:

DECLARE @HOURS decimal(7,4) = 25.5599
SELECT 
RIGHT('0' + CAST (FLOOR(@HOURS) AS VARCHAR), 2) + ':' + 
RIGHT('0' + CAST(FLOOR((((@HOURS * 3600) % 3600) / 60)) AS VARCHAR), 2) + ':' + 
RIGHT('0' + CAST (FLOOR((@HOURS * 3600) % 60) AS VARCHAR), 2)

输出:25:33:35

output : 25:33:35

-- 更新

十进制分钟到 24 小时以上

Decimal minutes to more than 24hrs

DECLARE @MINUTES decimal(7,4) = 77.9
SELECT
RIGHT('0' + CAST (FLOOR(COALESCE (@MINUTES, 0) / 60) AS VARCHAR (8)), 2) + ':' + 
RIGHT('0' + CAST (FLOOR(COALESCE (@MINUTES, 0) % 60) AS VARCHAR (2)), 2) + ':' + 
RIGHT('0' + CAST (FLOOR((@MINUTES* 60) % 60) AS VARCHAR (2)), 2);

输出:01:17:54

output: 01:17:54

这篇关于将十进制时间转换为小时和分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！