CODEWITHSUNDEEP
sql-server-2005timecase-statement
akwarywo
akwarywo

Reputation: 187

SQL Subtracting OverNight Time (returning only minutes) Get rid of negative time?

I have a bunch of start and end times:

    Start       End         Delta
    20:47:22    22:47:02    120
    22:49:12    0:48:47    -1321
    0:50:42     2:50:22     120
    ...
    23:11:07    2:10:44    -1261

The code I use is as follows (as time is a varchar)

  CONVERT(VARCHAR(8), DATEDIFF(minute, max(max_time), min(min_time)) ,114) 
  as delta_time,

But as you can see, because it is overnight the result is negative. Any thoughts about getting the right result of 119 minutes and 179 minutes (in the example above).

I thought of using a case statement that checks when the delta is negative. If it is negative, add 24 hours to the End time then try the subtracting.

 CASE WHEN DATEDIFF(minute, max(max_time), min(min_time)) < 0 THEN  
 DATEADD(minute, 1440, min(min_time)) 
 ELSE 0 END AS deltatest

However this code doesn't work, as SQL Server just changes the time to: 1900-01-02 00:48:47.000 (the next day, not something like 24:48:47, which is what I want). Any thoughts? Is there any method to add a convert to the CASE statement?? Thanks.

Upvotes: 0

Views: 296

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

You could always add 1440 to all of the computed deltas, then take the value mod 1440:

SELECT (120 + 1440) % 1440, (-1321 + 1440) % 1440

Result:

----------- -----------
120         119

Upvotes: 1

Related Questions