SQL Server:如何避免Datediff格式为小时的负值- Days:HH

Question

我需要以天为单位的结果:HH格式和以小时为单位的正值。

因为我从下面的查询中得到了负值0Day：-11Hr。这里- ad.notification_date是开始日期，ad.decision_letter是结束日期，Admit_codes ad是表名。

convert(varchar(5),DateDiff(s, ad.notification_date,ad.decision_letter)/86400)+' Day: ' 
+convert(varchar(5),DateDiff(s,ad.notification_date,ad.decision_letter)%86400/3600)+' Hr'as DAYs_Open from Admit_codes ad

我尝试了另外两个查询，如下所示。

select
    DateDiff(dd, ad.notification_date, ad.decision_letter) As days,  
    + DateDiff(hh, ad.notification_date, ad.decision_letter) % 24 As hours
from 
    #AG_ADMIT_CODES ad

select 
    datepart(day, ad.decision_letter - ad.notification_date) - 1 as days,
    datepart(hour, ad.decision_letter - ad.notification_date) as hours
from 
    #AG_ADMIT_CODES ad 
where 
    'Days' is not null
    and 'hours' is not null

请让我知道如何才能在天数:Hr格式中获得肯定的结果？

Answer 1

假设我的理解是正确的，但是使用ABS，它返回绝对值。所以：

SELECT DATEDIFF(DAY,'20180522','20180520'), --returns -2
       ABS(DATEDIFF(DAY,'20180522','20180520')); --returns 2