英文:
DATEADD function equivalent in Azure Data Factory
问题
我有一个SQL DATEADD语句，我正在尝试在Azure Data Factory (ADF)中复制，具体来说，在数据流中使用派生列。我的SQL代码如下：
DATEADD(SECOND, [TripHours] * 60.0 * 60.0, [TripTimeStamp])
我尝试将这个逻辑转换成ADF语法的方式如下：
add(TripTimeStamp, toInteger(multiply(TripHours, 60 * 60)))
TripTimeStamp列的类型为DateTime，TripHours是float类型。然而，我的ADF解决方案似乎只将几分钟添加到TripTimeStamp列，而不是预期的小时数。
列名已更改以保护隐私。但这只是一个示例。
| TripTimeStamp | TripHours | 预期结果 | 实际结果 |
|---|---|---|---|
| 2021-03-10 07:54:00.000 | 22.1 | 2021-03-11 06:00:00.000 | 2021-03-10 07:55:19.560 |
对于我可能做错的事情，是否有任何建议？
英文:
I have a SQL DATEADD statement that I'm trying to replicate in Azure Data Factory (ADF), specifically within a data flow using a derived column. My SQL code looks like this:
DATEADD(SECOND,[TripHours] * 60.0 * 60.0,[TripTimeStamp])
I tried to convert this logic into ADF syntax in the following way:
add(TripTimeStamp, toInteger(multiply(TripHours, 60 * 60)))
The TripTimeStamp column is of type DateTime and TripHours is a float type. However, my ADF solution only seems to add a couple of minutes to the TripTimeStamp column, not the expected number of hours.
The column names are changed for privacy. But this is an example.
| TripTimeStamp | TripHours | Expected Result | Actual Result |
|---|---|---|---|
| 2021-03-10 07:54:00.000 | 22,1 | 2021-03-11 06:00:00.000 | 2021-03-10 07:55:19.560 |
Any advice on what i might be doing wrong?
答案1
得分: 0
在原始的SQL代码中，我正在添加秒数。
DATEADD(SECOND,[TripHours] * 60.0 * 60.0,[TripTimeStamp])
然而，Azure Data Factory的“add”函数将时间解释为毫秒，而不是秒。因此，在Azure Data Factory中获得相同的结果，我必须调整原始查询以将时间转换为毫秒：
add(TripTimeStamp, toInteger(multiply(TripHours, 60 * 60 * 1000)))
英文:
Wow, okay typical that i solved it just after posting.
So in the original SQL code, i was adding seconds.
DATEADD(SECOND,[TripHours] * 60.0 * 60.0,[TripTimeStamp])
However, Azure Data Factory's 'add' function interprets time in milliseconds, not seconds. So, to get the same result in Azure Data Factory, I had to adjust the original query to convert the time to milliseconds:
add(TripTimeStamp, toInteger(multiply(TripHours, 60 * 60 * 1000)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论