CODEWITHSUNDEEP
sqlsql-serversql-server-2008timeduration
David Hayward
David Hayward

Reputation: 189

SQL - Calculating duration between TIME

I have the below two example appointments

enter image description here

The second appointment runs from 7PM to Midnight. However the duration comes back as 19 hours. This is due to the date not being stored and it calculating the differnence between 7PM and midnight of the previous night.

Appointments can only be scheduled up to Midnight anyway, so is there some SQL that will increase the day by 1 day when selecting to assume its midnight of the upcoming night and not the night before? This should only be affective when the time is midnight, otherwise it works fine.

Current SQL

select mr.AssignmentId, ABS(DATEDIFF(MINUTE, mr.AssignmentStart, mr.AssignmentEnd) /60.0) as Total

from master_rota mr

enter image description here

Upvotes: 0

Views: 296

Answers (4)

Isaac
Isaac

Reputation: 3363

Just add a day if AssignmentEnd is midnight (00:00).

SELECT mr.AssignmentId,
    CASE WHEN mr.AssignmentEnd = '00:00'
         THEN DATEDIFF(MINUTE, mr.AssignmentStart, DATEADD(DAY, 1, mr.AssignmentEnd)) / 60.0
         ELSE DATEDIFF(MINUTE, mr.AssignmentStart, mr.AssignmentEnd) / 60.0
    END AS Total
from master_rota mr;

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

If it is midnight, change to 00:00:00 the next day

select ABS(DATEDIFF(MINUTE, mr.AssignmentStart, iif(mr.AssignmentEnd='1900-01-01 00:00:00', '1900-01-02 00:00:00', mr.AssignmentEnd )) /60.0) as Total

from #temp mr

Upvotes: 0

Dan Field
Dan Field

Reputation: 21641

You should get your application doing the inserts to actually do the correct insert logic. However, it seems like this could be a workaround:

SELECT mr.AssignmentId
     , DATEDIFF(MINUTE, mr.AssignmentStart, 
         CASE WHEN mr.AssignmentEnd < mr.AssignmentStart THEN mr.AssignmentEnd + 1 ELSE mr.AssignmentEnd END) /60.0 as Total    
FROM master_rota mr

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Well, don't use abs():

select mr.AssignmentId,
       (case when mr.AssignmentStart < AssignmentEnd
             then DATEDIFF(MINUTE, mr.AssignmentStart, mr.AssignmentEnd) / 60.0
             else 24 + DATEDIFF(MINUTE, mr.AssignmentStart, mr.AssignmentEnd) / 60.0
        end) as Total
from master_rota mr;

Actually, I think I prefer this equivalent version:

select mr.AssignmentId,
       (DATEDIFF(MINUTE, mr.AssignmentStart, mr.AssignmentEnd) / 60.0 + 
        (case when mr.AssignmentStart < AssignmentEnd then 0 else 24 end)
       ) as Total
from master_rota mr;

It has the datediff() in only one place.

Upvotes: 3

Related Questions