Post reply

SSIS: get time difference between two columns (DT_WSTR)

  • September 15, 2022 at 11:06 pm

    #4088325

    I have the following two columns:

    Start_Time = 15:02:11 (DT_WSTR)
    End_Time = 15:02:50 (DT_WSTR)

    I need to write an SSIS expression for my derived column that will calculate the time difference in Seconds. Output should be:

    39

  • September 16, 2022 at 6:45 am

    #4088387

    Here is one way. It converts the complete times to seconds and then subtracts one from the other.

    ((DT_I4) TOKEN( @[User::End_Time] , ":",1 )*3600 +(DT_I4) TOKEN( @[User::End_Time] , ":",2 )*60 +(DT_I4) TOKEN( @[User::End_Time] , ":",3 ) ) - 
    ((DT_I4) TOKEN( @[User::Start_Time] , ":",1 )*3600 +(DT_I4) TOKEN( @[User::Start_Time] , ":",2 )*60 +(DT_I4) TOKEN( @[User::Start_Time] , ":",3 ))

    It assumes that End_Time is later than Start_Time and that no day boundaries are crossed.

    I created my formula using SSIS variables rather than column names, so you'll have to adjust it to accommodate that.

    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

  • September 16, 2022 at 10:43 am

    #4088456

    Thank you. It works 100%

Viewing 3 posts - 1 through 2 (of 2 total)

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