Difference between two dates in days, hours, minutes and seconds

Asked

Viewed 64,148 times

11

Problem: How to subtract between two dates and return the value in Days, Hours, Minutes and Seconds in an 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
sql sql-server datetime

  • See if this link clarifies your question http://msdn.microsoft.com/pt-br/library/ms189794.aspx

  • 2

    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.

  • 2

    Via Transactsql, use DATEDIFF. http://msdn.microsoft.com/en-us/library/ms189794.aspx

  • I made some calculations with date: http://adjuniordba.wordpress.com/2014/01/30/calculo-simples-de-data/ [] s

  • 1

    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/

5 answers

16


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');

  • ^^ hehehe took my doubts, only improving a little bit the query, by logic if I have a *@after shorter than in *@before I don’t have a busy day yet. The DATEDIFF has this bug when calculating the busy days, and that’s what was killing me.... I’m sorry if I didn’t express my doubt more clearly, because it had been all Friday cracking my head.

  • @Fredguapo, I changed the function so she could treat the negative values. I think it makes more sense and it’s simpler to change the values before, take a look at how it turned out. The change that you had suggested made that when you had a negative hour, you would show 1 negative day and 1 negative hour.

  • There is a failure in the above calculation, caused by the use of different granularities in the DATEDIFF function for the same calculation. The detailed explanation is in the article Dominating dates and times in SQL Server -> https://portosql.wordpress.com/2020/02/29/dominando-datas-horas/

5

This code is wrong in the calculation of the time difference. The time difference should be like this:

+ RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(MINUTE, @antes, @depois) / 60), 2) + ':'

For in 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)

1

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)

0

Even with the correction proposed in answer number 3, the function does not return the correct values. It follows the function with changes in the form of calculation:

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

  • When reading your answer I was in doubt whether this code is correct or only incorporates the correction proposed in answer 3. When referring to another post do so by indicating the name of the author, because the numbering of the answers depends on the sorting option.

  • Note that the number that appears in the reply is the number of votes and not the sequence number.

0

More compact version:

SELECT FORMAT(FLOOR(CAST(@depois-@antes AS FLOAT)),'0d ')+FORMAT(@depois-@antes,'hh:mm:ss')

MS, in a series of products, defines the date internally as the number of days since 1/1/1900. The difference between two dates results precisely in the difference in days.

Browser other questions tagged sql sql-server datetime

You are not signed in. Login or sign up in order to post.