Results 1 to 6 of 6

Thread: Query help!

  1. #1
    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. #2
    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
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote 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
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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