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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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 5 (of 5 total)

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