Number of hours, Not Time of Day

[antiskeptic]

Everything I have found so far is referring to time of day, or number of hours since a certain date, yadda, yadda, yadda. I need a format of 40:00 hours as opposed to 40.0 hours. The field I am deriving this from is a sum of minutes for a work week, for instance. In my Query, I am using a temp table to collect and sum the minutes/hours a person took for vacation / paid time off. so this is what I've been using, thinking it would work:

convert(varchar(8), dateadd(mi,sum(VacationActualTime),0),8)

then realizing that the dateadd that I was using, only works on a 12 hour cycle..and will not actually just give me the sum of hours into the format I need. *smacking head* DUH!
Does, anyone know how to do this? It's doesn't have to be a "real number"... simply a display for the number of hours calculated by my sum.
Hope I haven't confused too thouroughly. *ha*

Thank you in advance,
Antiskeptic

 

[hneal98]

If you want to just display the Hours:Minutes, you need to get the Hours and trucate the decimal part of it and convert it to a string. Then get the minutes, convert that to string and concatenate the two.

 

[NoCoolHandle]

What does the data look like in raw output and what do you want it to look like?

What type of field is vacationactualtime.?

(sorry got a little lost there - not enough caffeeeenn..)

 

[antiskeptic]

I realize that is what I need to do, but I'm a little dense on the syntax...which is why I wrote for help. *ha*
Anyone know the code for doing this?

~Antiskeptic

 

[hneal98]

As NoCoolHandle stated above, we need to know what kind of data you are working with.

 

[antiskeptic]

NoCoolHandle: I need to go from 40.0 (decimal) to this format 40:00

---the last post was in answer to hneal98.

Sincerely,
Antiskeptic

 

[antiskeptic]

Or I can go from 2400 minutes to 40:00 display...either way is fine by me...

Antiskeptic

 

[hneal98]

use the convert function to get the integer part of the hours like this:

declare myHours as string
declare myMinutes as string
set myHours = convert(integer, vacationactualtime))

--and then to get the minutes like this:

set myMinutes = convert(char(5),(vacationactualtime-myHours)*60)

select rtrim(convert(char(3),myHours)) + : + rtrim(convert(char(5),myMinutes))

I think I got it right. Might check out the ().

 

[SQLSister]

Are you getting the 2400 minutes right now?

Then write a function to convert it to hours and minutes and then format the result and return it as the result of the function.

Steps in the function

Input value is number of minutes an integer

Hours are calulcated by number of minutes divided by 60.

Minutes are the remainder inputMinutes - (hours*60)

Then concatenate your result and add the colon converting the result to a varchar value. You may also want to do some formatting to add leading zeroes.

Then return the final result of the concatenation as the result of your function.






Questions about posting. See faq183-874

 

[NoCoolHandle]

inputing

1.0 1.33330 12 1.
gets
1:00 1:33 12:00 1:20

Create Function ConvertTODateAndMin

( @i decimal(10,4))

returns varchar(300)
as
begin
declare @dot int,@howmanypast int,@string varchar(300)
select @string = cast(@i as varchar(300))
select @dot = charindex('.',@string)

if @dot > 0 
	begin
		select @string = replace(@String,'.',':')
		select @howmanypast = len(@string) - @dot
		if @howmanypast = 1 
			begin
				Select @string = @string + '0'
			end
		else if @howmanypast = 0
			begin
				Select @string = @string + '00'
			end
		else if @howmanypast >=2
			begin
				select @string = substring(@string,1,@dot + 2)
			end
	end
else
	begin
 		select @string = @string + ':00'
	end
return @string
end
go

select dbo.ConvertTODateAndMin(1.0), dbo.ConvertTODateAndMin(1.33330), dbo.ConvertTODateAndMin(12), dbo.ConvertTODateAndMin(1.2)

 

[antiskeptic]

Well, I am getting an arithmetic overflow with this solution. Is there any shorter way to do this? or is this the only way? I will keep fiddling with it. But I'm afraid this is not the solution that this query needs.

And unfortuneatly I can not share the code with you...it's much too long and would simply confuse more. I am simply taking the amount of minutes and hours in one temp table adding them together in another temp table and using that total to give me all the info about the employee along with the pay period processing date and how many days in that processing period were paid days off, etc, Then then decimal number of hours (40.0) and next is where I need the 40:00. I was hoping there would be a one line solution...if there isn't then I will keep working with what I have and what you've given me. Thank you

~Antiskeptic

 

[antiskeptic]

this last post was for hneal98...you others beat me to my post. *haha* I'll try your sugestions as well...I guess I got my answer. There is NOT a simple solution to this. I've never written a function before, so NoCoolHandle...I thank you for your post...I'll give it a shot.

Thank you,
Antiskeptic

 

[NoCoolHandle]

antiskeptic,
just execute the create function code in the database you want it to run in and then write you query like..

select dbo.ConvertTODateAndMin(sum(VacationActualTime)) from yourtable where whatever=somevalue

where sum(VacationActualTime)) gives you the decimal value you showed above.. and yourtable is the datasource etc...

 

[antiskeptic]

Well, I guess I'm just too scared to try a function just yet. *ha* Because I fiddled a little more and came up with this, and it works almost perfectly, and for my purposes it will fit the bill.

The reason I say almost is because I'm only getting 1 digit past the ':' instead of 2 whenever there are no minutes.

convert(varchar(4),round(sum(stoptime-starttime)/60,2),0)+':'+
convert(varchar(4),((round(sum(stoptime-starttime),2)-round(sum(stoptime-starttime)/60,2)*60)),0) starttimestoptime,

example of what I mean:

40 hours and 20 minutes comes through as 40:20
however, 40 hours only comes through as 40:0

But really this is great, and I'm glad to be able to get what I need. The bits of code from above helped me figure this out, so thank you all for your help!!!

Sincerely,
Antiskeptic