Convert decimal time to hours and minutes

Question

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.

Answer 1

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

Answer 2

This should work for you

DECLARE @f [real]
SET @f = 13.50

SELECT DATEADD(mi, (@f - FLOOR(@f)) * 60, DATEADD(hh, FLOOR(@f), CAST ('00:00:00' AS TIME)))