CODEWITHSUNDEEP
sqlsql-servert-sqldatediff
user8513344
user8513344

Reputation:

T-SQL DATEDIFF giving returning result 1 hours when difference is only 45 minutes

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

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.

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

Upvotes: 1

Views: 2335

Answers (1)

Marta B
Marta B

Reputation: 448

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

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

Upvotes: 4

Related Questions