CODEWITHSUNDEEP
sqlsql-server
ranto1
ranto1

Reputation: 43

Calculating time difference when times are cross midnight

I am trying to calculate the time difference where the times cross midnight.

(Difference between 09:00pm and 01:00am)

Using Microsoft SQL, does not recognise datetrunc().

Code using at the moment is datediff(minute, S.Start_, S.End_)/60

Upvotes: 1

Views: 2243

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

Just use a case expression:

(case when s.start_ < s.end_
      then datediff(hour, S.Start_, S.End_)
      else datediff(hour, S.End_, s.Start_)
 end)

Dividing the minutes by 60 and ignoring the remainder is the same as counting the hours.

Upvotes: 0

asmgx
asmgx

Reputation: 8014

Use this function

create function fn_MinuteDiff(@Start time, @End time)
returns int
as
begin
    if @End<@Start
    begin
        return 1440 - datediff(minute,@End, @Start) 
    end
    return datediff(minute, @Start, @End) 

end

then call it in your select statement

select dbo.fn_MinuteDiff (cast('9:00pm' as time), cast('1:00am' as time))

Upvotes: 0

Daniel Brughera
Daniel Brughera

Reputation: 1651

The best way is to have both values as DateTime, then you can get the real value, as a workaround you can assume that if the end time is lower than the start time, means that it refers to next day, but it will be limited to 23 hours (24 if you use <=) 

DECLARE @start TIME = '21:00'
DECLARE @end TIME = '01:00' 
SELECT DATEDIFF(HOUR, @start , @end) + CASE WHEN @end < @start THEN 24 ELSE 0 END

Upvotes: 2

Related Questions