Problem: How to perform the subtraction between two dates and return the value in Days, Hours, Minutes and Seconds in a SQL query with SQL Server 2014?
Example:
Dados:
DataRecebimento DataEnvio
2013-11-29 11:30:40.157 2014-05-27 14:10:50.637
2013-11-29 17:30:40.157 2014-05-27 14:10:50.637
Resultado pretendido (intervalo entre as datas):
179d 03:40:10
178d 21:40:10
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 as a function 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 (@ 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');
This code is wrong in calculating the time difference. The time difference should look like this:
+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(MINUTE, @antes, @depois) / 60), 2) + ':'
For the following example:
SELECT (SUM(DATEDIFF(HOUR,'2015-05-03 10:42:00','2015-05-03 12:00:00')) % 24)
The select above will return 2 hours and the select below will return correctly 1 hour, because the difference is 1 hour and 18 minutes.
SELECT (SUM(DATEDIFF(MINUTE,'2015-05-03 10:42:00','2015-05-03 12:00:00')) / 60)
Even with the fix proposed in response number 3, the function does not return the correct values. Here is the function with changes to the calculation form:
CREATE FUNCTION dbo.fnComputeDateDiff
(
@dtStartDate DATETIME, @dtEndDate DATETIME, @Mode CHAR
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @Temp DATETIME;
DECLARE @Days INT
DECLARE @Hours INT
DECLARE @Minutes INT
DECLARE @Seconds INT
DECLARE @TotalSeconds INT
DECLARE @Ret VARCHAR(20)
SET @Ret = ''
IF (@dtStartDate IS NOT NULL AND @dtEndDate IS NOT NULL AND @Mode IN ('H','D'))
BEGIN
IF (@dtStartDate > @dtEndDate)
BEGIN
SET @temp = @dtStartDate;
SET @dtStartDate = @dtEndDate;
SET @dtEndDate = @temp;
END
SET @TotalSeconds = DATEDIFF(SECOND, @dtStartDate, @dtEndDate)
SET @Days = @TotalSeconds / 86400
SET @TotalSeconds = @TotalSeconds % 86400
SET @Hours = @TotalSeconds / 3600
SET @TotalSeconds = @TotalSeconds % 3600
SET @Minutes = @TotalSeconds / 60
SET @Seconds = @TotalSeconds % 60
IF @Mode = 'H'
BEGIN
SET @Hours = @Hours + @Days * 24
SET @Days = 0
END
SET @Ret = CASE @Days WHEN 0 THEN '' ELSE RTrim( Convert(Char,@Days)) + ' d ' END
SET @Ret = @Ret + Right(RTrim(Convert(Char,@Hours)),10) + ':'
SET @Ret = @Ret + Right('00' + RTrim(Convert(Char,@Minutes)),2) + ':'
SET @Ret = @Ret + Right('00' + RTrim(Convert(Char,@Seconds)),2)
END
RETURN @Ret
END
GO
I found this solution to the problem of hours:
CONVERT(VARCHAR, ABS(DATEDIFF(SECOND, 2013-11-29 11:30:40.157, 2014-05-27 14:10:50.637) / 60 / 60 / 24))
+ 'd ' + RIGHT('00' + CONVERT(VARCHAR, ABS(((DATEDIFF(SECOND, 2013-11-29 11:30:40.157, 2014-05-27 14:10:50.637) / 60) / 60) % 24)), 2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR, ABS((DATEDIFF(SECOND, 2013-11-29 11:30:40.157, 2014-05-27 14:10:50.637) / 60) % 60)), 2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR, ABS(DATEDIFF(SECOND, 2013-11-29 11:30:40.157, 2014-05-27 14:10:50.637) % 60)), 2)
More compact version:
SELECT FORMAT(FLOOR(CAST(@depois-@antes AS FLOAT)),'0d ')+FORMAT(@depois-@antes,'hh:mm:ss')
MS, in a series of products, internally defines the date as the number of days since 1/1/1900. The difference between two dates results precisely in the difference of days.