convert decimal to time 6.80 =7.20 hrs in SQL

Question

Please help me to do this

decimal 6.80 should equal to 7.20 hours

DECLARE @R1 decimal(4,2);
DECLARE @R2 decimal(4,2);

declare @Type1 decimal(4,2);
declare @Type2 decimal(4,2);

DECLARE @R1Time decimal(4,2);
DECLARE @R2Time decimal(4,2);

SET @R1=2.5
SET @R2=3.5


SET @Type1=17;
SET @Type2=7;

SET @R1Time=(FORMAT((ISNULL(60.0/NULLIF(@R1,0),0)),'N2'))
SET @R2Time=(FORMAT((ISNULL(60.0/NULLIF(@R2,0),0)),'N2'))

SELECT @R1Time as R1Min
SELECT @R2Time as R2Min

SELECT FORMAT(((@Type1*@R1Time)/60.0),'N2') R1Hrs -- 6.80 hours this = 24*17=408/60
SELECT FORMAT(((@Type2*@R2Time)/60.0),'N2') R2Hrs

SELECT CONVERT(CHAR(5), DATEADD(MINUTE, 60*(convert(decimal(4,2),FORMAT(((@Type1*@R1Time)/60.0),'N2'))), 0), 108);--6.48 hours

6.80 hours this = 24*17=408/60 this should be 7.20 hours not 6.48 is it?. Did I am wrong please help me thanks

@R1 is how many in an hour 60/2.5 =24min per Type1 and 17 Type1 is 17*24 =408 min then convert to time –

6.8 hours does indeed equal 6 hours and 48 minutes. 6 hours and 80 minutes equals 7 hours and 20 minutes. I'm not sure what you're asking...? — Ian Nelson, Jan 03 '14 at 11:34
R1 is how many in an hour 60/2.5 =24min per Type1 and 17 Type1 is 17*24 =408 min then convert to time — aas, Jan 03 '14 at 11:47
So is your question "In T-SQL how can I format a decimal in hours and minutes, e.g. 6.80 becomes 6:48?" — Ian Nelson, Jan 03 '14 at 11:52
408 min is how many hours ? it should be 408/60= 6.48 why 6.80 ??? — aas, Jan 03 '14 at 11:57
@aas As the first comment already explains, 6.80 doesn't mean 6 hours and 80 minutes, and 6.48 doesn't mean 6 hours and 48 minutes. 408/60 = 6.8. That's just basic arithmetic, you can check it on pretty much any calculator. — , Jan 03 '14 at 12:01

Jean-Paul · Answer 1 · 2014-01-03T12:51:56.793

So what you are saying is that the number before the decimal seperator is correct (in hours) and the number after the decimal seperator is in the absolute amount of minutes?

Then in pseudocode you can do something like this:

INPUT = 6.80

HOURS = FLOOR(INPUT)
DECIMALS = (INPUT - HOURS) * 100

if ( DECIMALS > 60 )
{
     HOURS = HOURS + 1
     MINUTES = DECIMALS - 60
}

// Now hours and minutes are in the way you intended

Now all you have to do is convert this pseudocode into SQL code.

In function form, this would look something like this (I wrote this by heart so beware for typo's/ errors):

CREATE FUNCTION ChangeTime
-- Input current time decimal
   (@CurrentTime decimal(2,1) )
RETURNS decimal(2,1) -- New time
AS
BEGIN

   DECLARE @Hours int, @Minutes int;
   SET @Hours = FLOOR(@CurrentTime);
   SET @Minutes = (@CurrentTime - @Hours)*100;

   IF @Minutes >= 60 
   BEGIN
     SET  @Hours   =  @Hours   +  1; -- New hours
     SET  @Minutes =  @Minutes - 60; -- New minutes
   END

   RETURN ( @Hours + (@Minutes/100) ) -- New (corrected) time

END

On the other hand, if you mean: I need to convert the decimals such that 6.50 becomes 6 hours and 30 minutes and 6.80 becomes 6 hours and 48 minutes, then we can change the function to the following:

CREATE FUNCTION ChangeTime
-- Input current time decimal
   (@CurrentTime decimal(2,1) )
RETURNS decimal(2,1) -- New time
AS
BEGIN

   DECLARE @Hours int, @Minutes int;
   SET @Hours = FLOOR(@CurrentTime);
   SET @Minutes = (@CurrentTime - @Hours)*100;

   SET  @Minutes =  (@Minutes / 100) * 60; -- New minutes

   RETURN ( @Hours + (@Minutes/100) )       -- New (corrected) time

END

If both of these functions do not get the desired result, then please update your question with an explanation of what you exactly want to achieve.

Good luck!

I think you misunderstood the question. Both of your examples gives the wrong result — t-clausen.dk, Jan 03 '14 at 12:40
@t-clausen.dk: I corrected some code from my phone and then made a typo (wrote it all by heart). I corrected it and it should work now (I think the top one has the same logic as your answer; hence should give the same answer). — Jean-Paul, Jan 03 '14 at 12:47
@t-clausen.dk Then it must be some SQL syntax typo I made somewhere. But if you follow my pseudocode you can see it should give the same answer as you because it's based on the same logic: `6.80 becomes 7.20`. I just found another typo and corrected it. — Jean-Paul, Jan 03 '14 at 12:51

t-clausen.dk · Answer 2 · 2014-01-03T12:26:38.197

0

declare @hour decimal(6,2) = 6.8

select floor(@hour) + floor(@hour%1/.6) + @hour%1%.6

Result:

7.20

If you want it as a function:

CREATE FUNCTION f_convert(@hour decimal(6,2))
RETURNS decimal(6,2)
AS
BEGIN
RETURN floor(@hour) + floor(@hour%1/.6) + @hour%1%.6
END

Test(sqlserver 2008+):

SELECT dbo.f_convert(hour)
FROM (values (6.8),(3.9),(.59)) x(hour)

Result:

7.20
4.30
0.59

edited Jan 03 '14 at 12:26

answered Jan 03 '14 at 12:06

t-clausen.dk

43,517
12
59
92

convert decimal to time 6.80 =7.20 hrs in SQL

2 Answers2

Linked