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

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

1. ### Moonwalker2000New 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
@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

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