SQL Server Performance

Calculate time between two dates in hours:minutes:seconds.

Discussion in 'SQL Server 2005 General Developer Questions' started by Moonwalker2000, Jul 31, 2008.

  1. Moonwalker2000 New Member

    Trying to use datediff drove me nuts and is ok of you have two dates from the same day. But try calculating the time for an index build for example that occurs 1 second either side of midnight. The Datediff for day, hour, minute all show 1. The only true value I found is to use Datediff on second between the two dates and then calculate the hours, minutes and seconds based on this value. Proc code below. Anybody see any issues? Thanks Create procedure [dbo].[DWS_sp_CalcTimeTaken] @StartDateTime
    Datetime, @EndDateTime Datetime
    , @Duration varchar(10) output
    AS/******
    Date: 07-25-2008
    Version: 1.2
    DESC: Calculate time taken from a supplied Start and End time and give result as 00:00:00
    ******/DECLARE
    @Hours varchar(2), @Minutes varchar(2), @Seconds varchar(2), @TotalSeconds varchar(10)SELECT
    @TotalSeconds = DATEDIFF(second, @StartDateTime,@EndDateTime)-- CALCULATE_HOURSSELECT
    @Hours = @TotalSeconds / (60*60) -- we are chopping off the rest.IF
    (Select LEN(@Hours)) = 1 BEGIN
    Select @Hours = '0' + @HoursEND-- CALCULATE_MinutesSELECT
    @Minutes = (@TotalSeconds - (@Hours * (60*60))) / 60IF
    (Select LEN(@Minutes)) = 1 BEGIN
    Select @Minutes = '0' + @MinutesEND-- CALCULATE_SecondsSELECT
    @Seconds = @TotalSeconds - ((@Minutes * 60) + (@Hours * (60*60)))IF
    (Select LEN(@Seconds)) = 1 BEGIN
    Select @Seconds = '0' + @SecondsEND
    select @Duration = @Hours + ':' + @Minutes + ':' + @Seconds
    ----------------------- Run the proc -----set nocount on Declare@StartDateTime Datetime
    , @EndDateTime Datetime
    Select @StartDateTime = '2008-07-27 23:59:59.000'Select
    @EndDateTime = '2008-07-28 00:00:01.000'Declare @Duration varchar(10)
    EXEC [DWS_ETL].[dbo].[DWS_sp_CalcTimeTaken] @StartDateTime, @EndDateTime, @Duration output
    Select @Duration
  2. Adriaan New Member

    You should anticipate the interval to exceed 100 hours, so don't limit the hours to two characters.
    This may be a little shorter than what you have:
    declare @start datetime, @end datetime, @duration varchar(36),
    @hours varchar(30), @minutes varchar(2), @seconds varchar(30)
    set @start = .............
    set @end = .............

    set @seconds = abs(datediff(second, @start, @end))
    set @hours = @seconds / 3600
    set @minutes = (@seconds - (@hours * 3600)) / 60
    set @seconds = (@seconds - (@hours * 3600) - (@minutes * 60))
    if cast(@hours as int) < 10 set @hours = '0' + @hours
    if cast(@minutes as int) < 10 set @minutes = '0' + @minutes
    if cast(@seconds as int) < 10 set @seconds = '0' + @seconds
    set @duration = @hours + ':' + @minutes + ':' + @seconds
  3. Moonwalker2000 New Member

    Thanks Adriaan.
    I had thought about limiting hours to less than 100 but I put this together to start tracking DBA jobs (backups, index builds etc) for a reporting setup I’m writing. Anything taking longer than 100 hours would be a real problem for a DBA job but I see your point also.
    Thanks for the shorter code. Looks great.
    Patrick

Share This Page