当差异仅为45分钟时，T-SQL DATEDIFF给出返回结果1小时

Facing an issue with T-sql Datediff function, I am calculating date difference in minutes between two dates, and then in hours.

面对T-sql Datediff函数的问题，我计算两个日期之间的分钟数，然后是几小时。

Minute is giving me correct result

分钟给了我正确的结果

SELECT DATEDIFF(minute,'2018-01-22 23:59:00.000','2018-01-23 00:44:00.000')
Result 45 minutes

But when I am trying to calculate hours it's giving me incorrect results for days that are almost over and new day begins, So if the time parameter is '23:59:00' and the second parameter is '00:44:00' it returns 1 hour difference when its only 45 minutes.

但是当我试图计算小时数时，它会给我几乎结束且新的一天开始的日期不正确的结果，所以如果时间参数是'23：59：00'，第二个参数是'00：44：00'它只有45分钟时返回1小时差异。

SELECT DATEDIFF(HOUR,'2018-01-22 23:59:00.000','2018-01-23 00:44:00.000')
Result 1 Hour --Incorrect

I am expecting this result to be zero

我期待这个结果为零

SELECT DATEDIFF(HOUR,'2018-01-22 23:59:00.000','2018-01-23 00:44:00.000')
Result 0 Hour -- This is the result expected

Update: Posting my Function here if anyone needs to Calculate difference between two dates in format as Day:Hour:Minute

更新：如果有人需要计算两个日期之间的差异，请在此处发布我的函数，格式为日：小时：分钟

ALTER FUNCTION [dbo].[UDF_Fedex_CalculateDeliveryOverdue] 
(
    -- Add the parameters for the function here
    @requiredDate VARCHAR(50), 
    @deliveryStamp VARCHAR(50)
)
RETURNS VARCHAR(25)
AS
BEGIN

DECLARE @ResultVar VARCHAR(25)

 SET @ResultVar = ( SELECT CASE WHEN a.Days = 0 AND a.Hours = 0 THEN CAST(a.Minutes AS VARCHAR(10)) + ' Minutes'
                                WHEN a.Days = 0  THEN CAST(a.Hours AS VARCHAR(10)) + ' Hours ' + CAST(a.Minutes AS VARCHAR(10)) + ' Minutes'
                                ELSE CAST(a.Days AS VARCHAR(10)) +' Day ' + CAST(a.Hours AS VARCHAR(10)) +' Hours ' + CAST(a.Minutes AS VARCHAR(10)) + ' Minutes'

                                    END 

FROM ( SELECT DATEDIFF(hh, @requiredDate,@deliveryStamp)/24 AS 'Days'
        ,(DATEDIFF(MI, @requiredDate,@deliveryStamp)/60) - 
        (DATEDIFF(hh, @requiredDate,@deliveryStamp)/24)*24 AS 'Hours'
        ,DATEDIFF(mi, @requiredDate,@deliveryStamp) - 
        (DATEDIFF(mi, @requiredDate,@deliveryStamp)/60)*60 AS 'Minutes'
        ) a)
    -- Return the result of the function

    RETURN @ResultVar
END

1 个解决方案

#1

To get value 0 you need to get the result in minutes, and convert to hours:

要获得值0，您需要在几分钟内得到结果，并转换为小时：

SELECT DATEDIFF(minute,'2018-01-22 23:59:00.000','2018-01-23 00:44:00.000')/60

For more precision:

为了更精确：

SELECT DATEDIFF(minute,'2018-01-22 23:59:00.000','2018-01-23 00:44:00.000')/60.0

#1