Subtract two time values in SQL Server

Viewed 52722

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

Please help.

4 Answers

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)

Related