A diferença de hora do SQL entre duas datas resulta em hh: mm: ss

Estou enfrentando alguma dificuldade em calcular a diferença de tempo entre duas datas.

O que eu quero é, eu tenho duas datas, digamos

@StartDate = '10/01/2012 08:40:18.000' @EndDate='10/04/2012 09:52:48.000' 

então a diferença entre duas datas na forma de hh:mm:ss é 72:42:30 .

Como posso obter esse resultado em uma consulta T-SQL?

 declare @StartDate datetime, @EndDate datetime select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000' select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss] 

Esta consulta será útil para você.

O código mais curto seria:

 Select CAST((@EndDateTime-@StartDateTime) as time(0)) '[hh:mm:ss]' 

Embora talvez não seja o mais eficiente, isso funcionaria:

 declare @StartDate datetime, @EndDate datetime select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000' select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) 

Se você pode executar dois selects, então isso seria melhor porque você só faz o datediff uma vez:

 declare @StartDate datetime, @EndDate datetime select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000' declare @Sec BIGINT select @Sec = DateDiff(s, @startDate, @EndDate) select convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60)) 

Se você não se opuser ao casting de tipo implícito, oferecerei uma solução alternativa. É mais legível com melhor formatação? Você é o juíz.

 DECLARE @StartDate datetime = '10/01/2012 08:40:18.000' ,@EndDate datetime = '10/04/2012 09:52:48.000' SELECT STR(ss/3600, 5) + ':' + RIGHT('0' + LTRIM(ss%3600/60), 2) + ':' + RIGHT('0' + LTRIM(ss%60), 2) AS [hh:mm:ss] FROM (VALUES(DATEDIFF(s, @StartDate, @EndDate))) seconds (ss) 
 DECLARE @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12' SELECT CAST((@dt2-@dt1) as time(0)) 

Eu gosto da idéia de transformar isso em uma function, para que ela se torne reutilizável e suas consultas fiquem mais fáceis de ler:

 --get the difference between two datetimes in the format: 'h:m:s' CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate) DECLARE @difference VARCHAR(10) = CONVERT(VARCHAR(4), @seconds / 3600) + ':' + CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':' + CONVERT(VARCHAR(2), @seconds % 60) RETURN @difference END 

Uso:

 DECLARE @StartDate DATETIME = '10/01/2012 08:40:18.000' DECLARE @endDate DATETIME = '10/04/2012 09:52:48.000' SELECT dbo.getDateDiff(@startDate, @endDate) AS DateDifference 

Resultado:

  DateDifference 1 73:12:30 

Também é mais fácil ler o resultado se você adicionar preenchimento para que o formato seja sempre hh:mm:ss . Por exemplo, aqui está como você faria isso no SQL Server 2012 ou posterior:

 --get the difference between two datetimes in the format: 'hh:mm:ss' CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate) DECLARE @difference VARCHAR(10) = FORMAT(@seconds / 3600, '00') + ':' + FORMAT(@seconds % 3600 / 60, '00') + ':' + FORMAT(@seconds % 60, '00') RETURN @difference END 

Observe que isso não cortará a hora se ela tiver mais de dois dígitos. Então 1 hora apareceria como 01:00:00 e 100 horas apareceria como 100:00:00

Dê uma olhada nestes. Eu não usei mais parênteses para mantê-lo legível, então lembre-se que a multiplicação é feita antes da adição ou subtração.

Ambos abaixo retornam:

 hr mins sec timediff 73 12 30 73:12:30 

Isso é escrito para não usar uma subconsulta e ser o mais legível e compreensível:

 declare @StartDate datetime, @EndDate datetime set @StartDate = '10/01/2012 08:40:18.000' set @EndDate = '10/04/2012 09:52:48.000' select datediff(hour, @StartDate, @EndDate) hr, datediff(minute, @StartDate, @EndDate) - datediff(hour, @StartDate, @EndDate) * 60 mins, datediff(second, @StartDate, @EndDate) - (datediff(minute, @StartDate, @EndDate) * 60) sec, cast(datediff(hour, @StartDate, @EndDate) as varchar)+':'+ cast(datediff(minute, @StartDate, @EndDate) - datediff(hour, @StartDate, @EndDate) * 60 as varchar)+':'+ cast(datediff(second, @StartDate, @EndDate) - (datediff(minute, @StartDate, @EndDate) * 60) as varchar) timediff 

Esta é uma versão que teria melhor desempenho se você tivesse muitos dados. Requer uma subconsulta.

 declare @StartDate datetime, @EndDate datetime set @StartDate = '10/01/2012 08:40:18.000' set @EndDate = '10/04/2012 09:52:48.000' select s.seconds / 3600 hrs, s.seconds / 60 - (seconds / 3600 ) * 60 mins, s.seconds - (s.seconds / 60) * 60 seconds, cast(s.seconds / 3600 as varchar) + ':' + cast((s.seconds / 60 - (seconds / 3600 ) * 60) as varchar) + ':' + cast((s.seconds - (s.seconds / 60) * 60) as varchar) timediff from (select datediff(second, @StartDate, @EndDate) as seconds) s 

Eu me deparei com este post hoje enquanto eu estava tentando reunir a diferença de tempo entre os campos localizados em tabelas separadas unidas em um campo-chave. Este é o código de trabalho para tal empreendimento. (testado no sql 2010) Bare em mente que minha consulta original co-juntou 6 tabelas em um keyfield comum, no código abaixo eu removi as outras tabelas para não causar nenhuma confusão para o leitor.

O objective da consulta é calcular a diferença entre as variables ​​CreatedUTC & BackupUTC, onde a diferença é expressa em dias e o campo é chamado ‘DaysActive’.

 declare @CreatedUTC datetime declare @BackupUtc datetime SELECT TOP 500 table02.Column_CreatedUTC AS DeviceCreated, CAST(DATEDIFF(day, table02.Column_CreatedUTC, table03.Column_EndDateUTC) AS nvarchar(5))+ ' Days' As DaysActive, table03.Column_EndDateUTC AS LastCompleteBackup FROM Operations.table01 AS table01 LEFT OUTER JOIN dbo.table02 AS table02 ON table02.Column_KeyField = table01.Column_KeyField LEFT OUTER JOIN dbo.table03 AS table03 ON table01.Column_KeyField = table03.Column_KeyField Where table03.Column_EndDateUTC > dateadd(hour, -24, getutcdate()) --Gathers records with an end date in the last 24 hours AND table02.[Column_CreatedUTC] = COALESCE(@CreatedUTC, table02.[Column_CreatedUTC]) AND table03.[Column_EndDateUTC] = COALESCE(@BackupUTC, table03.[Column_EndDateUTC]) GROUP BY table03.Column_EndDateUTC, table02.Column_CreatedUTC ORDER BY table02.Column_CreatedUTC ASC, DaysActive, table03.Column_EndDateUTC DESC 

A saída será como segue:

 [DeviceCreated]..[DaysActive]..[LastCompleteBackup] --------------------------------------------------------- [2/13/12 16:04]..[463 Days]....[5/21/13 12:14] [2/12/13 22:37]..[97 Days].....[5/20/13 22:10] 

É uma cópia de escrita de script, em seguida, escreva em seu arquivo de script e altere o campo requerido e saia

 DECLARE @Sdate DATETIME, @Edate DATETIME, @Timediff VARCHAR(100) SELECT @Sdate = '02/12/2014 08:40:18.000',@Edate='02/13/2014 09:52:48.000' SET @Timediff=DATEDIFF(s, @Sdate, @Edate) SELECT CONVERT(VARCHAR(5),@Timediff/3600)+':'+convert(varchar(5),@Timediff%3600/60)+':'+convert(varchar(5),@Timediff%60) AS TimeDiff 
 DECLARE @StartDate datetime = '10/01/2012 08:40:18.000' ,@EndDate datetime = '10/10/2012 09:52:48.000' ,@DaysDifferent int = 0 ,@Sec BIGINT select @Sec = DateDiff(s, @StartDate, @EndDate) IF (DATEDIFF(day, @StartDate, @EndDate) > 0) BEGIN select @DaysDifferent = DATEDIFF(day, @StartDate, @EndDate) select @Sec = @Sec - ( @DaysDifferent * 86400 ) SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss] END ELSE BEGIN SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss] END ---------------------------------------------------------------------------------- dd HH:MM:SS 9d 1:12:30 
 declare @StartDate datetime, @EndDate datetime select @StartDate = '2016-05-04 10:23:41.083',@EndDate='2016-05-04 10:25:26.053' select CAST(DateDiff(MI, @startDate, @EndDate)/60 AS varchar)+':'+Cast(DateDiff(MI, @startDate, @EndDate)%60 AS varchar)+':'+cast(DateDiff(s, @startDate, @EndDate)%60 AS varchar) as [hh:mm:ss] 

isso vai te ajudar também

declarar @StartDate datetime, @EndDate datetime

 set @StartDate = '10/01/2012 08:40:18.000' set @EndDate = '10/04/2012 09:52:48.000' SELECT CONVERT(CHAR(8), CAST(CONVERT(varchar(23),@EndDate,121) AS DATETIME) -CAST(CONVERT(varchar(23),@StartDate,121)AS DATETIME),8) AS TimeDiff 
 declare @StartDate datetime; declare @EndDate datetime; select @StartDate = '10/01/2012 08:40:18.000'; select @EndDate='10/04/2012 09:52:48.000'; select cast(datediff(hour,@StartDate,@EndDate) as varchar(10)) + left(right(cast(cast(cast((@EndDate-@StartDate) as datetime) as time) as varchar(16)),14),6)