Convert decimal time to hours and minutes

sql-server, sql-server-2008, t-sql, time

Solution

You can try:

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

output : 20:33:35

But remember : Type Time in MSSQL only under 24hrs

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)

output : 25:33:35

-- Update

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

output: 01:17:54

Problem

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. 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 Have tried: ``` cast(cast(floor(fdsViewTimesheet.perStandardHours) as float(2))+':'+cast(floor(100*( fdsViewTimesheet.perStandardHours - floor(fdsViewTimesheet.perStandardHours)))as float(2)) as time) AS STANDARD_HOURS ``` 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.

Original source