 ## 3 replies

Last post Dec 26, 2019 03:41 PM by limno

• ### How to diff time on SQL Server

Hello All,

In my database, I have two values. How to subtract in SQL Server as time and get as 3.20 and 14.15?

TotalTime     EnteredLog

10.00                   6.40

40.10                   25.55

Thanks,

Sandeep

Please let me know if you need more help.

Regards,

Sandeep
• ### Re: How to diff time on SQL Server

#### Das.Sandeep

In my database, I have two values. How to subtract in SQL Server as time and get as 3.20 and 14.15?

Your question is a missing information.  What data type is TotalTime and EnteredLog?  When asking SQL questions, always post the table schema and code that reproduces the issue so the community is not guessing.

```DECLARE @TotalTime decimal(5,3) = 10.000
DECLARE @EnteredLog decimal(5,3) = 6.400

DECLARE @decimalMinutes decimal(5,2)

SET @decimalMinutes = (round(@TotalTime, 0, 1)*60 + ((@TotalTime %1 ) * 100)) - (round(@EnteredLog, 0, 1)*60 + ((@EnteredLog %1 ) * 100))

--standard decimal form
SELECT @decimalMinutes

--convert to custom format
DECLARE @hours INT = @decimalMinutes / 60
DECLARE @minutes INT  = @decimalMinutes - @hours * 60
DECLARE @result decimal(5,3) = @hours + CAST((@minutes / 100.00) AS decimal(5,3))

SELECT @result
```

• ### Re: How to diff time on SQL Server

Das.Sandeep,

#### Das.Sandeep

In my database, I have two values. How to subtract in SQL Server as time and get as 3.20 and 14.15?

You can create a custom function, then add logic to this function.

About how to create a custom function you can refer to this link: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver15

```IF OBJECT_ID (N'dbo.CalculateTime', N'FN') IS NOT NULL
DROP FUNCTION CalculateTime;
GO
CREATE FUNCTION dbo.CalculateTime(@start decimal(10,2),@end decimal(10,2))
RETURNS float
AS
BEGIN
declare @Result float
declare @decimalpart float
declare @intpart int
set @decimalpart= ((@start % 1)*100 +60-(@end%1)*100)%60
if ((@start % 1)*100)<((@end%1)*100)
begin
Set @intpart=convert(int,@start)-1-convert(int,@end)
end
else
begin
Set @intpart=convert(int,@start)-convert(int,@end)
end
set @Result=convert(float,@intpart)+convert(float,@decimalpart)/100
return @Result
END;

go
create table #a
(
starttime decimal(10,2),
endtime decimal(10,2)
)

insert into #a values(10.00,6.40),(40.10,25.55)

select starttime,endtime,dbo.CalculateTime(starttime,endtime) as Result from #a
```

The result: Best regards,

Sam

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• ### Re: How to diff time on SQL Server

```create table test (TotalTime varchar(5), EnteredLog  varchar(5))
Insert into test values
('10.00','6.40')
,('40.10','25.55')

--create table test (TotalTime decimal(4,2)
--,EnteredLog  decimal(4,2))
--Insert into test values
--(10.00,6.40)
--,(40.10,25.55)

Select