Reputation: 31
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
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
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