How to subtract two time values in SQL Server 2008. I am using time variables in a stored procedure.
Please help.
How to subtract two time values in SQL Server 2008. I am using time variables in a stored procedure.
Please help.
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)