1

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

Joakim Danielson
  • 43,251
  • 5
  • 22
  • 52
ranto1
  • 43
  • 7
  • 2
    Possible duplicate of [Calculate time difference in minutes in SQL Server](https://stackoverflow.com/questions/26991807/calculate-time-difference-in-minutes-in-sql-server) – Udhay Titus May 14 '19 at 04:10
  • You can use datediff itself. Why do you want to change it ? – Venkataraman R May 14 '19 at 04:29
  • use DATEDIFF as like `SELECT DATEDIFF(MINUTE, '2019-05-14 21:00' , '2019-05-15 01:00') AS MinuteDiff` [for more reference](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017) – Udhay Titus May 14 '19 at 04:30
  • 1
    date part is required. otherwise if the end time is 10 PM next day, this will return 1 hr where as it should be 1Day(24 Hr) + 1 Hr. – mkRabbani May 14 '19 at 05:01

3 Answers3

2

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
Daniel Brughera
  • 1,641
  • 1
  • 7
  • 14
0

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))
asmgx
  • 7,328
  • 15
  • 82
  • 143
0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786