calculate time difference between two dates in ssis

Question

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!!

Jarvisjary

Answer 1

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

• DATEDIFF (SSIS Expression)
• Convert Seconds to HH:MM:SS using SSIS
• SSIS Expression – Convert Seconds to HHH:MM:SS Format

Geaghan

Thank you! unfortunately I'm getting an error saying the RIGHT functions requires 2 arguments? Is that anything you can help with? Thanks in advance! – Jarvisjary

It can also be an expression that turns "23" into 00:23:00.0000000 or "61" into 01:01:00.0000000 if that is easier? I hope you can help me thanks alot ! :) – Jarvisjary

@ImperialBert Problem Solved , had an extra ")" by mistake . – Geaghan

Answer 2

There is no direct function that gives you the expected output, you have to get the difference between both dates in the minimal unit you want (seconds or milliseconds) then you should build your own expression that convert it to HH:mm:ss format)

You can use the following expression to get the difference between two dates:

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

Rogelioroger