0

I am having an issue converting a decimal such as 0.33333, which needs to show as 20 minutes. Using the formula I have it is showing as 19 minutes. I hope someone can assist please. Thank you. Currently the code I am using example.

declare @value float = 0.3333333
select
Cast(CONVERT(VARCHAR, CONVERT(INT, Floor(cast(@VALUE as Decimal (10,2))))) 
            + '.' + CONVERT (VARCHAR, CONVERT(INT, (cast(@value as Decimal (10,2)) - Floor(cast(@VALUE as Decimal (10,2)))) * 60.0)) as Decimal(10,2)) [hh.mm],
            cast(@value as money) [DecimalHours]
jarlh
  • 42,561
  • 8
  • 45
  • 63
user2364055
  • 33
  • 5
  • What happens if you keep more decimals, and skip floor? – jarlh Aug 06 '20 at 08:39
  • 3
    Money data type for time values? – jarlh Aug 06 '20 at 08:39
  • 4
    Well, that's because it's 19.999998 minutes and not 20. Probably you need to round instead of floor. – Robby Cornelissen Aug 06 '20 at 08:41
  • 2
    Does this answer your question? [Convert decimal time to hours and minutes](https://stackoverflow.com/questions/17788283/convert-decimal-time-to-hours-and-minutes) – GSerg Aug 06 '20 at 08:42
  • Thank you for the link, the problem with this one is that when I am dealing with hours over 24 hours it doesn't show correctly. For example I may have 498.3333 as a figure which would then need to display as 498.20 ideally. Thank you I will try the rounding – user2364055 Aug 06 '20 at 08:57

2 Answers2

1

Probably a lot easier when you use a date/time function:

SELECT FORMAT( DATEADD(n,ROUND(60.0 * @value,0) ,0) , 'HH:mm')
Wouter
  • 2,881
  • 2
  • 9
  • 22
  • Thank you but this doesn't quite work for anything over 24 hours. So a figure like 498.08 returns as 18:05. This needs to show as 498.05. The below solution from dnoeth achieves this. Thank you again – user2364055 Aug 06 '20 at 10:22
1
 -- integer part      -- fractional part             -- final rounding
cast(floor(@value) + ((@value - floor(@value)) * 0.6) as dec(18,2))

Works for more than 24 hours

dnoeth
  • 59,503
  • 4
  • 39
  • 56