日期差异格式为天数小时分秒

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

有没有办法获得两个日期之间的剩余时间(差异)?

For example I'd like to have it return 6 days, 10 hours, 3 minutes and 37 seconds.

例如,我想让它返回6天,10小时,3分钟和37秒。

3 个解决方案

#1

The following select 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 output is:

另一个更接近您想要的输出的选项是:

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 ANSWER (04/12/2018): Accounts for difference in lower order date part that affects the higher order date part (i.e. 23 hour difference will now result in 0 days 23 hours)!

更新的答案(04/12/2018):影响较高订单日期部分的较低订单日期部分的差异(即23小时差异现在将导致0天23小时)!

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

#2

Best way to display duration in MS SQL DD:HH:MM:SS for all records in the table

在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]

#3

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

尝试使用datediff函数,以秒为单位获得差异。然后除以得到天数等。

#1