How to get the difference between two times with SQL Server?

user_945836 0
Viewed 21925

I have the following two column with Time values

;WITH cte AS (
SELECT *
FROM (VALUES
('08:00:00','18:30:00'),
('20:00:00','08:00:00'),
('18:30:00','06:00:00'),
('08:00:00','18:30:00')) as t(time1, time2)
)

How can I get the following difference values:

result
 10.5
  12
 11.5
 10.5

I tried with DATEDIFF but I am never able to get a true values especially for the case from 18:30 to 6:00 I tried like this, but does not work for this case(8:30 to 6:00) ...

CASE
  WHEN GirisSaati > CikisSaati THEN cast( DATEDIFF(MINUTE, cast(CikisSaati as time ), cast(GirisSaati as time ))as float) / 60  
  WHEN GirisSaati <= CikisSaati THEN  cast( DATEDIFF(MINUTE,cast( GirisSaati as time ), cast(CikisSaati as time ) )as float) /60  
END

Hope to find help, thanks ...

sql-server
4 Answers

I found another option...

Step 1 -> Create this SQL function

CREATE FUNCTION [dbo].[get_TimeDefferance_hh_mm_ss]
(
    -- Add the parameters for the function here
    @FromTime time,@ToTime time
)
RETURNS time
AS
BEGIN
    declare @totalsec decimal(10,2)= datediff(SECOND,@FromTime,@ToTime);
    declare @secondPart int= @totalsec%60
    --select @secondPart As secondpart
    declare @totalminsOnly int= (@totalsec-@secondPart)/60

declare @MinsPart int=@totalminsOnly%60
--select @MinsPart AS minsPart

declare @totalHoursOnly int= (@totalminsOnly-@MinsPart)/60

--select @totalHoursOnly AS totalhoursonly

-- Return the result of the function
RETURN cast(@totalHoursOnly AS varchar(50))+':'+cast(@MinsPart as varchar(50))+':'+cast(@secondPart as varchar(50))

END

Step 2->Go to new query and check is it working..

declare @FromTime time='2:30:41';
declare @ToTime time='4:20:30';

declare @timeDiff time= **dbo.get_TimeDefferance_hh_mm_ss(@FromTime,@ToTime)**  
select @timeDiff

output--- 01:49:49.0000000

user_115656550

Maybe it is a 'bit' later, but I will post my solution in order to help someone else.

set @from = '10:15';
set @to = '12:30';

select (time_to_sec(timediff(@to, @from))/3600);

Output is 2.2500

user_132151110
Related