Search code examples
sqlsql-serverssisexpressionsql-server-data-tools

calculate time difference between two dates in ssis

I have the following two columns:

StartDate = 2017-01-01 00:00:00.000
EndDate = 2017-01-01 05:45:00.000

I need to write an SSIS expression for my derived column that will calculate the time between these two datetimes. Output should be:

05:45:00.0000000

Can anyone help with writing this expression?

Thanks in advance!!

Solution

  • You can use DATEDIFF() function to get the difference between two dates.

    difference in Hours

    DATEDIFF("Hh",[StartDate],[EndDate])

    difference in minutes

    DATEDIFF("mi",[StartDate],[EndDate])

    difference in minutes

    DATEDIFF("ss",[StartDate],[EndDate])

    Suggested Expression to return HH:mm:ss

    You have to get the difference in seconds then use the following expression

    RIGHT("000" + (DT_WSTR,3)(DATEDIFF("ss",@[User::StartDate],@[User::EndDate]) / 3600),3) + ":" + RIGHT("00" + (DT_WSTR,2)((DATEDIFF("ss",@[User::StartDate],@[User::EndDate]) % 3600) / 60)   ,2) + ":" + RIGHT("00" + (DT_WSTR,2)(DATEDIFF("ss",@[User::StartDate],@[User::EndDate])% 60),2)

    References