Subtract two time values in SQL Server

Question

How to subtract two time values in SQL Server 2008. I am using time variables in a stored procedure.

Please help.

Compost

Answer 1

You can use DATEDIFF():

SELECT DATEDIFF(Day, startDate, endDate)
  FROM table

SELECT DATEDIFF(Second, date, GETDATE())
  FROM table

Sibella

for using DATEDIFF() varible should be in datetime? – Compost

@Vyas What datatype do you have? – Sibella

@Vyas - It works for time datatype - e.g. SELECT DATEDIFF(Second, CAST('11:24:18.3500000' as time),CAST('12:24:18.3500000' as time)) – Claytor

According to MSDN documentation (found at msdn.microsoft.com/en-us/library/ms189794.aspx) the last two parameters are "an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value." – Edgell

Answer 2

DECLARE @END TIME = '16:00:00.0000000' ,    
     @START TIME = '01:00:00.0000000'
     SELECT convert(TIME,dateadd(ms,DateDiff(ss, @START, @END )*1000,0),114)

Koehn

Answer 3

following expression works for me

declare @starttime Time, @endtime Time
set @starttime='18:45'
set @endtime='22:45'
select DATEDIFF(HH,@starttime, @endtime)

output: 4

Melioration

Answer 4

Even You are using offset value or normal dates this code will give you appropriate answers.

   BEGIN TRY
DECLARE @OffSetVal1 VARCHAR(39) = '2019-12-02 09:15:29 +14:00'
      , @OffSetVal2 VARCHAR(39) = '2019-12-02 09:15:29 +12:30'
      , @minutes INT = 0
      , @OffSetDiff VARCHAR(19) = ''
      , @HourDiff INT = 0

SET @HourDiff = DATEDIFF(HOUR,@OffSetVal1,@OffSetVal2) -- To Check digits of hours.
SET @minutes = DATEDIFF(MINUTE,@OffSetVal1,@OffSetVal2) -- To Convert minutes to hours.
SET @OffSetDiff = @minutes / 60 + (@minutes % 60) / 100.0 -- To Check '+' Or '-' And To Get Value. 


SELECT CASE WHEN CAST(@OffSetDiff AS FLOAT) <= 0  
            THEN (CASE WHEN @HourDiff < 10
                      THEN FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
                 ELSE FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00') 
                 END)
        ELSE (CASE WHEN @HourDiff < 10
                      THEN '+'+FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
                 ELSE '+'+FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00') 
                 END) 
        END

END TRY
BEGIN CATCH
PRINT N'It seems you provided an invalid DateTimeOffSet Parameter. '
END CATCH

ANS :- +01.30 (# The offset differences with hour and minute and if you don't want that '+' simply remove from code & then run the code)

Engel