CODEWITHSUNDEEP
sqlsql-server
Athit Upakan
Athit Upakan

Reputation: 123

How to get time diff between 2 times

I have 2 columns with data type Time.

Ex:

08:00:00 - 12:00:00 = 4
08:30:00 - 23:00:00 = 15.5
20:00:00 - 00:00:00 = 4
22:00:00 - 06:00:00 = 8

Upvotes: 0

Views: 457

Answers (5)

Ullas
Ullas

Reputation: 11556

Use DATEDIFF

Query

declare @t1 as time = '08:30:00';
declare @t2 as time = '23:00:00';

select abs(cast((datediff(minute, @t1, @t2)) as float) / 60);

Result

14.5

And you can change the variables, @t1 and @t2 to your column names.

Update

You can use a CASE expression to check when the first time is greater that second time column value. If it is greater then the second time column should be in the next day.

Query

select
case when t1 > t2 then 
cast(
    datediff(
        minute,
        cast(
            cast(
                getdate() as date
            )
            as varchar(50)
        )
        + ' ' +
        cast(
            cast(
                left(t1, charindex('.', t1, 1) - 1)
                as varchar(50)
            )
            as datetime
        ), 
        cast(
            cast(
                getdate() + 1 as date
            )
            as varchar(50)
        )
        + ' ' +
        cast(
            cast(
                left(t2, charindex('.', t1, 1) - 1)
                as varchar(50)
            )
            as datetime
        )
    )
    as float
) / 60
else cast((datediff(minute, t1, t2)) as float) / 60 end
from [your_table_name];

Find demo here

Upvotes: 1

Shekhar Pankaj
Shekhar Pankaj

Reputation: 9145

DECLARE @time1 TIME ='22:00:00'
DECLARE @time2 TIME ='06:00:00'

SELECT CEILING(CAST(Diff as decimal)/(60 * 60)) FROM (SELECT  
CASE WHEN CAST(DATEDIFF(SECOND,@time1,@time2) AS INT) > 0 THEN  CAST(DATEDIFF(SECOND,@time1,@time2) AS INT)
ELSE (ABS(CAST(DATEDIFF(SECOND,CAST('23:59:59' AS TIME),@time1) AS INT)) + ABS(CAST(DATEDIFF(SECOND,@time2,CAST('00:00:00' as TIME)) AS INT)))
END AS Diff) AS A

UPDATE

The above rounds the result to next Hour using ceiling function, for hours in fractional part use below snippet

DECLARE @time1 TIME ='023:30:00'
DECLARE @time2 TIME ='06:00:00'

SELECT Round(CAST(Diff as decimal)/(60 * 60),2) FROM (SELECT  
CASE WHEN CAST(DATEDIFF(SECOND,@time1,@time2) AS INT) > 0 THEN  CAST(DATEDIFF(SECOND,@time1,@time2) AS INT)
ELSE (ABS(CAST(DATEDIFF(SECOND,CAST('23:59:59' AS TIME),@time1) AS INT)) + ABS(CAST(DATEDIFF(SECOND,@time2,CAST('00:00:00' as TIME)) AS INT)))
END AS Diff) AS A

Upvotes: 1

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

Use DATEDIFF Function

as next

select datediff(hour,'08:00:00','12:00:00')

Result:-

4

UPDATE

for getting absolute (Positive) value use ABS function as next

select abs(datediff(hour,'22:00:00','06:00:00'))

Result:-

16

UPDATE2:-

declare 
        @StartTime datetime = '22:00:00',
        @EndTime datetime = '06:00:00'


if (datepart(hour,@StartTime) >= 12)
set @StartTime = dateadd(day,-1,@StartTime)

select abs(datediff(hour,@StartTime,@EndTime))

Result:-

8

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

All of the answers suggesting to use ABS seem to be mis-thinking the situation here. If the result is negative then that means that the times crossed a date boundary, and the simple remedy there is to add 24 hours to the result to get the correct value.

Since you only have times, we have to assume that any given pair of times represent a period of less than 24 hours (otherwise, there's no means of distinguishing a pair of times that represent a 2 hour interval from a pair of times that represent a 26 hour interval).

So, what we can do is to always add 24 hours to the result and then take the result modulo 24 hours.

Here's the calculation in minutes:

SELECT (DATEDIFF(minute,FirstValue,SecondValue) + (24*60)) % (24*60)

Upvotes: 3

udhaya kumar
udhaya kumar

Reputation: 169

DECLARE @start time;
DECLARE @end time;
DECLARE @null time;

SET @start = '09:06:00';
SET @end = '21:27:00';
SET @null = '00:00:00';

SELECT DATEADD(SECOND, - DATEDIFF(SECOND, @end, @start), @null)

Upvotes: 1

Related Questions