SQL Server Converting Decimal into Time hh:mm, I need the total hours-CodePudding

I need to be able to get the total hours and minutes if it is over 24 hours from a decimal column in SQL Server.

This is the code I am using:

CAST(CAST(DATEADD(SECOND, 1654.86 * 60, 0) AS Time(0)) AS VARCHAR(5))

Since it's over 24 hours the output is "03:34" I would like for it to be "27:34" or if possible to tell me it will take 3 working days and "03:34" (not sure how that would work).

Thank you in advance! Paul

CodePudding user response：

As explained in the comments, the time data type is not designed to represent any sort of interval or timespan, it is only designed to represent clock time. As such, it is not capable of displaying 27 hours. Instead you need to build this string yourself with methods other than simple CAST as type:

DECLARE @d table(decimal_column decimal(15,2));

INSERT @d(decimal_column) VALUES(1654.86);

SELECT d.decimal_column, 
       nice_time = CONVERT(varchar(11), FLOOR(h))   ':' 
                     RIGHT('0'   CONVERT(varchar(11), FLOOR(m)), 2)
FROM @d AS d
CROSS APPLY
(
  VALUES(d.decimal_column/60, d.decimal_column%60)
) AS extracted(h,m);

Results:

decimal_column	nice_time
1654.86	27:34

Example db<>fiddle

You may have edge cases where you want actual rounding logic instead of FLOOR() - but if you have those cases, include them in your question so we know the desired output.

CodePudding user response：

select CONVERT(VARCHAR, CONVERT(INT, 1654.86/60))   ':' 
  CONVERT(VARCHAR, CONVERT(INT, 1654.86-(CONVERT(INT, 1654.86/60)*60)))

You can create a function for this query, it is very performance. Because we will not use the same operations in multi sections.