计算两个日期在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:

我需要为我的派生列编写一个SSIS表达式，该表达式将计算这两个日期时间之间的时间。输出应该是:

05:45:00.0000000

Can anyone help with writing this expression?

有人能帮我写这个表达吗?

Thanks in advance!!

提前谢谢! !

2 个解决方案

#1

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

您可以使用DATEDIFF()函数来获得两个日期之间的差异。

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

建议表达式返回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)
• DATEDIFF(ssi表达式)
• Convert Seconds to HH:MM:SS using SSIS
• 将秒转换为HH:MM:SS使用SSIS
• SSIS Expression – Convert Seconds to HHH:MM:SS Format
• SSIS表达式——将秒转换为HHH:MM:SS格式

#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)

没有直接的函数给你预期的输出，你必须在你想要的最小的单位(秒或毫秒)中得到两个日期之间的差异，然后你应该建立你自己的表达式把它转换成HH:mm:ss格式)

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)