How to divide a time type variable by 2 in SQL Server

user_8032896 0
Viewed 1937

I have a variable of type TIME, I need to divide the time of this variable by 2.

My SQL code:

DECLARE @TEMPO AS TIME = '01:31:00'

SELECT (@TEMPO / 2 ) AS RESULT

--the result should be: 00:45:30

SQL Server however throws an error:

Operand type clash: time is incompatible with int

Every help is welcome!

tsql sql-server
5 Answers

Using TIMEFROMPARTS(SQL Server 2012+):

DECLARE @TEMPO AS TIME = '01:31:00';
DECLARE @i INT = DATEDIFF(s, '00:00:00',@TEMPO)/2;

SELECT  TIMEFROMPARTS(@i/3600%60, @i/60%60 ,@i%60,0,0);
-- 00:45:30

db<>fiddle demo

user_50708790

Just another option is to convert the time to datetime and then to float, and then divide by two.

Example

DECLARE @TEMPO AS TIME = '01:31:00'

Select convert(time,convert(datetime,convert(float,convert(datetime,@Tempo))/2))

Returns

00:45:30.0000000
user_15700000

The time data type represents a Time Of Day, not an amount of time or Duration.

You can't divide a time of day by an integer, it isn't a concept that makes sense. What is 2 o'clock divided by 2?

If you are trying to represent a duration or amount of time, then store it as an integer of your lowest level of granularity, and then divide it. Then if you want you can display it in a formatted string of Hours:Minutes.

user_15075660 
DECLARE @TEMPO AS TIME = '01:31:00';
SELECT CONVERT(TIME,DATEADD(SECOND,DATEDIFF(SECOND,'0:0',@TEMPO)/2,'0:0')) AS [RESULT];
user_41195990

You can convert the time into an integer number of milliseconds using DateDiff, perform the arithmetic, and then use DateAdd to convert back to a Time:

declare @Tempo as Time = '01:31:00';
select @Tempo as Tempo;
set @Tempo = DateAdd( ms, DateDiff( ms, 0, @Tempo ) / 2, 0 );
select @Tempo as HalfTempo;
user_925460
Related