CODEWITHSUNDEEP
sqlsql-server-2008-r2data-conversion
Paul Yang
Paul Yang

Reputation: 31

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

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

Upvotes: 0

Views: 1205

Answers (2)

Ramin Faracov
Ramin Faracov

Reputation: 3313

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.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

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

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.

Upvotes: 2

Related Questions