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