# Thread: Query help!

1. Registered User
Join Date
Apr 2004
Posts
101

## Unanswered: Query help!

I have the following code to get the sql execution time.
I'm looking for the output like this 'Duration is 2 min 02 sec

Any help would be greately appreciated.

declare @sql varchar(100),
@StartTime datetime,
@EndTime datetime
set @sql = 'Duration - '
set @StartTime =CONVERT(char(50), GETDATE(), 9)

exec dbo.sp_helpdb

Waitfor delay '00:02:02'
set @EndTime =CONVERT(char(50), GETDATE(), 9)
set @sql = @sql + ltrim(rtrim(convert(char(10),datediff(ss,@StartTim e,@EndTime)))) -- + ' minutes'
exec master..xp_sendmail 'xxx.com',
@subject = @sql
go

2. Registered User
Join Date
Dec 2002
Posts
1,245
This is (I'm sure) hideously inefficient code and probably also fugly. However, I think it gets you an approximation of what you want:

Code:
```declare
@msg varchar(100),
@StartTime datetime,
@EndTime datetime,
@hh int,
@mm int,
@ss int

set @msg = 'Duration - '
set @StartTime =CONVERT(char(50), GETDATE(), 9)

exec dbo.sp_helpdb

Waitfor delay '00:00:01'
set @EndTime =CONVERT(char(50), GETDATE(), 9)

select @ss = datediff(ss, @StartTime, @EndTime)
-- select @ss = 355
IF @ss > 3600
BEGIN
SELECT @hh = @ss / 3600
SELECT @ss = @ss - (@hh * 3600)
SELECT @mm = @ss / 60
SELECT @ss = @ss - (@mm *60)
SELECT @msg = 'Duration - ' + cast(@hh as varchar(5)) + ' hrs, '  + cast(@mm as varchar(5)) + ' mins, '  + cast(@ss as varchar(5)) + ' secs'
END
ELSE
IF @ss > 60
BEGIN
SELECT @mm = @ss / 60
SELECT @ss = @ss - (@mm *60)
SELECT @msg = 'Duration - ' + cast(@mm as varchar(5)) + ' mins, '  + cast(@ss as varchar(5)) + ' secs'

END
ELSE
BEGIN
SELECT @msg = 'Duration - ' + cast(@ss as varchar(5)) + ' secs'
END
-- exec master..xp_sendmail 'xxx.com',
-- @subject = @msg
print @msg```

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I like to use the modulo operator:
Code:
```declare	@Seconds int
set	@Seconds = datediff(seconds, @StartTime, @EndTime)
select	'Duration is ' + cast(@Seconds/60 as varchar(2)) + ' min ' + cast(@Seconds % 60 as varchar(2)) + ' sec'```

4. Registered User
Join Date
Dec 2002
Posts
1,245
Originally Posted by blindman
I like to use the modulo operator:
Show off!

Seriously; I think too linearly and forget to look for the elegant solution.

Regards,

hmscott

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'm just lazy.
Code:
```DECLARE @dStart		DATETIME

SET @dStart = GetDate()

WAITFOR DELAY '00:00:10'

SELECT Convert(CHAR(12), DateAdd(ms, DateDiff(ms, @dStart, GetDate())
,  '0:00:00'), 14) AS milliseconds
,  Convert(CHAR(8), DateAdd(ms, DateDiff(ms, @dStart, GetDate())
,  '0:00:00'), 14) AS 'hh:mm:ss'```
-PatP

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Heck, the laziest solution would be to just subtract the starttime from the endtime and then cast the result in whatever standard format he desires.
Code:
```declare	@StartTime datetime
declare	@EndTime datetime

set	@StartTime = '2005-11-11 10:04:07.797'
set	@EndTime = '2005-11-11 11:07:45.797'

select	'Duration - ' + right(convert(varchar(20), dateAdd(second, DateDiff(second, @StartTime, @EndTime), 0), 120), 8)
--or even this...
select	'Duration - ' + right(convert(varchar(50), @EndTime - @StartTime, 120), 8)```
You do NOT want to get into a lazy contest with me. Man, I'll just tell the freakin'g user to look at his own watch....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•