user1030181 user1030181 - 4 months ago 10
SQL Question

How to get difference between two dates to get result something like Result as 2 Days, 23 hours, 56 mins

How to get difference between two dates in sql server 2012. I am using this select statement. I want result something as

select (getdate()-2) - (GetDATE()) as 'Diff'

Result as 2 Days, 23 hours, 56 mins

Answer
DECLARE @DAT1 datetime = '20160401 13:10:23';
DECLARE @DAT2 datetime = '20160405 12:15:22';


select FLOOR(ABS(datediff(SECOND, @dat1,@dat2)) / 24 / 3600) as 'DAYS', 
       FLOOR(ABS(datediff(SECOND, @dat1,@dat2)) / 3600) % 24 'HOURS',
       FLOOR(ABS(datediff(SECOND, @dat1,@dat2)) / 60) % 60 'MINUTES',
       ABS(datediff(SECOND, @dat1,@dat2))  % 60 'SECONDS'

in that case

    select CAST(FLOOR(ABS(datediff(SECOND, @dat1,@dat2)) / 24 / 3600) as nvarchar(10)) + 
        ' DAYS, ' + 
        CAST(FLOOR(ABS(datediff(SECOND, @dat1,@dat2)) / 3600) % 24 as nvarchar(2)) +  
        ' HOURS, ' + 
        CAST((ABS(datediff(SECOND, @dat1,@dat2)) / 60) % 60  as nvarchar(2)) + 
        ' MINUTES';