DECLARE @antes DATETIME = '2013-11-29 11:30:40.157';
DECLARE @depois DATETIME = '2014-05-27 14:10:50.637';
SELECT CONVERT(VARCHAR, DATEDIFF(DAY, @antes, @depois)) + 'd '
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(HOUR, @antes, @depois) % 24), 2) + ':'
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(MINUTE, @antes, @depois) % 60), 2) + ':'
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(SECOND, @antes, @depois) % 60), 2);
Or in function form below (with additional treatments):
CREATE FUNCTION dbo.fnCalculaTempo
(
@antes DATETIME, @depois DATETIME
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @temp DATETIME;
IF (@antes > @depois)
BEGIN
SET @temp = @antes;
SET @antes = @depois;
SET @depois = @temp;
END
RETURN CASE WHEN @temp IS NULL THEN '' ELSE '-' END
+ CASE WHEN DATEDIFF(DAY, @antes, @depois) <> 0 THEN CONVERT(VARCHAR, DATEDIFF(DAY, @antes, @depois)) + 'd ' ELSE '' END
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(HOUR, @antes, @depois) % 24), 2) + ':'
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(MINUTE, @antes, @depois) % 60), 2) + ':'
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(SECOND, @antes, @depois) % 60), 2);
END
GO
Tests with negative values (@after before @before):
SELECT dbo.fnCalculaTempo('2013-11-29 11:30:40.157', '2014-05-27 14:10:50.637');
SELECT dbo.fnCalculaTempo('2013-11-29 17:30:40.157', '2014-05-27 14:10:50.637');
SELECT dbo.fnCalculaTempo('2013-11-29 17:30:40.157', '2013-11-29 18:34:00.249');
SELECT dbo.fnCalculaTempo('2014-11-29 11:30:50.157', '2014-11-29 10:30:50.637');
SELECT dbo.fnCalculaTempo('2014-11-29 10:30:50.157', '2014-11-28 10:30:40.637');
See if this link clarifies your question http://msdn.microsoft.com/pt-br/library/ms189794.aspx
– abfurlan
Welcome to Stack Overflow! Please explain the problem better, and if possible include a example of code that reproduces what is happening, because your question is too wide. Use Mysql, Sqlserver, etc? See Help Center How to Ask.
– Jorge B.
Via Transactsql, use
DATEDIFF
. http://msdn.microsoft.com/en-us/library/ms189794.aspx– OnoSendai
I made some calculations with date: http://adjuniordba.wordpress.com/2014/01/30/calculo-simples-de-data/ [] s
– Advaldo Paiva Moreira
When using calculations with seconds it is necessary to be careful not to occur overflow. In the article Calculate elapsed time between two dates you find a solution that is also available as a window function. -> https://portosql.wordpress.com/2020/04/calcular-time_elapse_betweentwo dates/
– José Diz