Calculate running time (start and stop = duration)?

[NavMen]

Hi,

Can someone help me with the following:

We like to calculate the time (duration) between Start time and End time of a process, we created two tables with the names “sscreate1” DataType=datetime as Start Time and “sscomp1” DataType=datetime as End Time.

I have no idea how the SQL queries must look like to calculate the duration in H, Min, Sec

Thanks,

NM

 

[SQLSister]

First get the differnce in seconds since that is the lowest thing you want. DAtediff is the system function you want to use.

Now write a function to convert seconds to hours and minutes and calculate the remaining seconds. Then concatenate all the results together into a string variable.
That will be the return value of your function.


Questions about posting. See faq183-874

 

[obislavu]

As the previous post said:

CREATE FUNCTION [dbo].[fn_CalcDuration] (@start datetime,@end datetime)  
RETURNS varchar(8)  AS
BEGIN 
DECLARE @retval as varchar(8)
DECLARE @tsec as integer
DECLARE @h as integer
DECLARE @m as integer
DECLARE @s as integer
DECLARE @rest as integer
DECLARE @space1 as varchar
DECLARE @space2 as varchar
select @tsec=datediff(second, @start, @end)
select @h =@tsec/3600  /* hours */
select @rest=@tsec - @h*3600
select @m=@rest/60   /* minutes */
select @s=@rest - @m*60   /* seconds */
SELECT   @space1 = CASE len(@m)
		 WHEN 1 THEN REPLICATE('0', 1)
                            WHEN 2 THEN ''
		 END
SELECT   @space2 = CASE len(@s)
		 WHEN 1 THEN REPLICATE('0', 1)
                            WHEN 2 THEN ''
		 END

select @retval= cast(@h as varchar(2)) + ':' + @space1+ cast(@m as varchar(2)) + ':' + @space2 + cast(@s as varchar(2)) 
return @retval
END

The results :
select * , dbo.fn_CalcDuration(sscreate1, sscomp1) as duration from _tUsers

sscreate1 sscomp1 duration
----------------------- ----------------------- ---------
2004-09-01 10:30:30.000 2004-09-01 11:00:01.000 0:29:31
2004-09-02 10:30:59.000 2004-09-02 11:00:00.000 1:29:01

-obislavu-

 

[NavMen]

Obislavu,

Thanks for your input, the best solution I have received!

/Navmen

 

[vongrunt]

Another way:

create function fn_CalcDuration( @start datetime, @end datetime)
returns varchar(8) as
begin
	return convert( varchar(8), dateadd(ss, datediff(ss, @start, @end), 0), 108 )
end

 

[donutman]

Let the system do the work:

[Blue]DECLARE[/Blue] @Start [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]DECLARE[/Blue] @End [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]SET[/Blue] @Start[Gray]=[/Gray][red]'01/03/2005  23:45'[/red]
[Blue]SET[/Blue] @End[Gray]=[/Gray][red]'01/04/2005 1:25'[/red]
[Blue]SELECT[/Blue] [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8[Gray])[/Gray][Gray],[/Gray]@End[Gray]-[/Gray]@Start[Gray],[/Gray]108[Gray])[/Gray]

As long as it's not more than 24 hours the above will do the conversion and you don't need to create a UDF that will only hurt performance.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Dang... I always forget that date1 - date2 = yet another date

 

[ProdAir]

if you want to get your time in seconds (which you can then convert into any time format you like i.e. hours, minutes, days), I suggest using the timer function.

 

[fireant]

Obislavu,

can the result in your function be more than 24hrs? I sampled the function and it returned the value *:57:05. Why is the first parameter only an asterisk? It's not showing the hour at all...

Thanks,
Louie

Louie C.