Calculating difference between two times with a twist (between 9am and 5pm)

  • I am seeking help on this one. I've tinkered and experimented and can't seem to connect the dots here.

    I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.

    I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).

    For example, here's an easy example:

    OPEN: 9:00:00

    CLOSE: 17:00:00

    8 Hours/480 minutes

    I could get this easy enough with a DATEDIFF function.

    But what about:

    OPEN: 08:00:00

    CLOSE: 18:00:00

    10 Hours total but only 8 of those 10 are within 9am-5pm.

    Or what about:

    OPEN: 10:00:00

    CLOSE: 20:00:00

    10 Hours total but only 7 are within 9am-5pm range.

    I can calculate the total hours/minutes between the two times but not within that special range. I'm pretty sure I should use DATEDIFF but just can't seem to connect the dots in head.

    Any suggestions??

  • Maybe some brute force can help you.

    CREATE TABLE #Test(

    OpenTimetime,

    CloseTimetime)

    INSERT #Test

    SELECT '09:00:00', '17:00:00' UNION ALL

    SELECT '08:00:00', '18:00:00' UNION ALL

    SELECT '10:00:00', '20:00:00'

    SELECT *,

    DATEDIFF(MI, CASE WHEN OpenTime < '09:00:00' THEN '09:00:00' ELSE OpenTime END

    ,CASE WHEN CloseTime > '17:00:00' THEN '17:00:00' ELSE CloseTime END) AS [Minutes],

    DATEDIFF(HH, CASE WHEN OpenTime < '09:00:00' THEN '09:00:00' ELSE OpenTime END

    ,CASE WHEN CloseTime > '17:00:00' THEN '17:00:00' ELSE CloseTime END) AS [Hours]

    FROM #Test

    GO

    DROP TABLE #Test

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I was actually leaning towards a temp table....I will give it a try.

  • RedBirdOBX (3/25/2014)


    I was actually leaning towards a temp table....I will give it a try.

    Note that the temp table I used is to have the sample data. You don't need to create it as part of your solution. It would be nice if you provide your sample data in this format so we don't waste time on creating it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I'll post back what I came up with and welcome feedback. Hopefully within the hour.....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply