We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

## 3 replies

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

• Das.Sandeep

Star

8783 Points

2327 Posts

### 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
• mgebhard

All-Star

53661 Points

24018 Posts

### 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
```

• samwu

Contributor

3370 Points

1409 Posts

### 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

IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
• limno

All-Star

123252 Points

10024 Posts

Moderator

### 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