Date difference formatted in days.

Question

Is there a way to get the remaining time (difference) between two dates?

For example, I would like it to return after 6 days, 10 hours, 3 minutes and 37 seconds.

Answer 1

According to this answer, the following choice should work:

DECLARE @x int, 
        @dt1 smalldatetime = '1996-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

SET @x = datediff (s, @dt1, @dt2)


SELECT convert(varchar, @x / (60 * 60 * 24)) + ':'
+ convert(varchar, dateadd(s, @x, convert(datetime2, '0001-01-01')), 108)

Another option that is closer to your desired result:

DECLARE @start DATETIME ,
    @end DATETIME,
    @x INT

SELECT  @start = '2009-01-01' ,
        @end = DATEADD(ss, 5, DATEADD(mi, 52, DATEADD(hh, 18, DATEADD(dd, 2, @start)))),
        @x = DATEDIFF(s, @start, @end)

SELECT  CONVERT(VARCHAR, DATEDIFF(dd, @start, @end)) + ' Days '
        + CONVERT(VARCHAR, DATEDIFF(hh, @start, @end) % 24) + ' Hours '
        + CONVERT(VARCHAR, DATEDIFF(mi, @start, @end) % 60) + ' Minutes '
        + CONVERT(VARCHAR, DATEPART(ss, DATEADD(s, @x, CONVERT(DATETIME2, '0001-01-01')))) + ' Seconds'

UPDATED RESPONSE (04/12/2018): Credentials for differences in the lower part of the lower case that affect part of a higher order date (i.e. the difference in 23 hours will now be 0 days 23 hours)!

DECLARE @start DATETIME = '2018-04-12 15:53:33' ,
@end DATETIME = '2018-04-13 14:54:32' ,
@x INT;

SET @x = DATEDIFF(s, @start, @end);

SELECT  CONVERT(VARCHAR(10), ( @x / 86400 )) + ' Days '
        + CONVERT(VARCHAR(10), ( ( @x % 86400 ) / 3600 )) + ' Hours '
        + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) / 60 ))
        + ' Minutes ' + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) % 60 ))
        + ' Seconds';

Answer 2

MS SQL DD: HH: MM: SS

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 23:40:18.000',@EndDate='10/04/2012 04:10:48.000'

select  convert(varchar(5),DateDiff(s, @startDate, @EndDate)/86400)+':' +convert(varchar(5),DateDiff(s, @startDate,@EndDate)%86400/3600)+':'
+convert(varchar(5),DateDiff(s, @startDate,@EndDate)%3600/60)+':'

+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss] 

Answer 3

Try using the lateiff function , getting the difference in seconds. Then divide to get the number of days, etc.