Post reply

Subtract time passed midnight

  • January 25, 2023 at 3:24 pm

    #4141698

    Hello all! I hope everyone's great

    I have these columns here that are hour and minute for entry and for exit. I also have a column that let us know if it passed midnight or not. I would like to know the time spent. Here's what I have:

    Capture

    My first approach (and I don't know how to do it) is to have a column for Entry Date&Time and another for Exit Date&Time, where I would subtract it and maybe I would get the time spent. Notice that the column "EXIT_SESSION_D" show us if the exit time is passed midnight.

    Can anyone help me?

    Thanks in advance

  • January 25, 2023 at 3:27 pm

    #4141699

    Reassemble the (date, hour, minute, second) back into a proper date and then use DATEDIFF?

    Failing that, you'd have to check if ExitHour < EntryHour, and then use (24 + ExitHour - EntryHour)

  • January 25, 2023 at 4:01 pm

    #4141722

    I just did a few sample rows, I'm not going to type in all that data.

    DROP TABLE IF EXISTS #data;
    CREATE TABLE #data ( ENTRY_HOUR tinyint NULL, ENTRY_MINUTE tinyint NULL, EXIT_SESSION_D bit NULL, EXIT_HOUR tinyint NULL, EXIT_MINUTE tinyint NULL )
    INSERT INTO #data VALUES
        (12, 45, 1,  0, 38),
        (14,  6, 1,  0, 18),
        (10, 44, 0, 20, 36),
        (11,  5, 0, 23, 59)

    SELECT ENTRY_HOUR, ENTRY_MINUTE, EXIT_SESSION_D, EXIT_HOUR, EXIT_MINUTE, 
        CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, ENTRY_DATETIME, EXIT_DATETIME), 0) AS time(0)) AS ELAPSED_TIME
    FROM #data
    CROSS APPLY (
        SELECT DATEADD(MINUTE, ENTRY_MINUTE, DATEADD(HOUR, ENTRY_HOUR, CAST(0 AS datetime))) AS ENTRY_DATETIME,
            DATEADD(MINUTE, EXIT_MINUTE, DATEADD(HOUR, EXIT_HOUR + CASE WHEN EXIT_SESSION_D = 0 THEN 0 ELSE 24 END, CAST(0 AS datetime))) AS EXIT_DATETIME
    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 25, 2023 at 4:26 pm

    #4141724

    pedroccamara wrote:

    Hello all! I hope everyone's great

    I have these columns here that are hour and minute for entry and for exit. I also have a column that let us know if it passed midnight or not. I would like to know the time spent. Here's what I have:

    Capture

    My first approach (and I don't know how to do it) is to have a column for Entry Date&Time and another for Exit Date&Time, where I would subtract it and maybe I would get the time spent. Notice that the column "EXIT_SESSION_D" show us if the exit time is passed midnight. Can anyone help me? Thanks in advance

    Scott has shown you how to post your sample data in a form which can be easily used by others. Please do this in future, rather than expecting someone else to do it for you.

  • January 25, 2023 at 5:19 pm

    #4141746

    Thanks a lot ScottPletcher

    Most helpful

  • January 25, 2023 at 5:21 pm

    #4141748

    You're absolutely right Phil. Next time I will add the SQL.

    Thank you for letting me know.

Viewing 6 posts - 1 through 6 (of 6 total)

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