CODEWITHSUNDEEP
sql-server
LFB
LFB

Reputation: 175

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

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

Upvotes: 5

Views: 30485

Answers (4)

Даяна Димитрова
Даяна Димитрова

Reputation: 103

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

Upvotes: 0

MSK Perera
MSK Perera

Reputation: 51

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

Upvotes: 0

David Rushton
David Rushton

Reputation: 5030

This is because you are dividing by 60, an integer. This will return an integer result. Instead try dividing by a numeric:

Example

DECLARE @Time1 TIME(0) = '08:00:00';
DECLARE @Time2 TIME(0) = '18:30:00';

SELECT      
    DATEDIFF(MINUTE, @Time1, @Time2) / 60       AS Incorrect,
    DATEDIFF(MINUTE, @Time1, @Time2) / 60.0     AS Correct

;

Result

Incorrect   Correct
10          10.500000

This is a consequence of data type precedence. From MSDN:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

In this case INT has the highest precedence.

Alternatively you could use CAST to explicitly set the data type: CAST(60 AS DECIMAL(18,2)).

Upvotes: 7

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is how you can do it:

DECLARE @t table(time1 time, time2 time)
INSERT @t values
('08:00','18:30'), 
('20:00','08:00'),
('18:30','06:00'),
('08:00','18:30')   

;WITH CTE as
(
  SELECT 
    time1, time2,
    CASE WHEN time2 < time1 THEN 1 ELSE 0 END 
      + CAST(time2 as datetime) - CAST(time1 as datetime) diff
FROM @t
)
SELECT 
  time1, 
  time2, 
  cast(diff as time) timediff, 
  cast(diff as float) * 24 decimaldiff
FROM CTE

Result:

time1  time2  timediff  decimaldiff
08:00  18:30  10:30     10.5
20:00  08:00  12:00     12
18:30  06:00  11:30     11.5
08:00  18:30  10:30     10.5

Upvotes: 5

Related Questions