DATEADD function equivalent in Azure Data Factory

Question

DATEADD function equivalent in Azure Data Factory

Jonathan Holén 10

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.

TripTimeStampTripHoursExpected ResultActual Result2021-03-10 07:54:00.00022,12021-03-11 06:00:00.0002021-03-10 07:55:19.560 Any advice on what i might be doing wrong?

1 answer

Your answer

Answer 1

Jonathan Holén 10

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

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-07-13T16:52:54.1+00:00

Hello Jonathan Holén

Glad you were able to solve the issue, and thank you for sharing the resolution, as it helps the community members who are looking for answers to similar questions.

Share via

DATEADD function equivalent in Azure Data Factory

1 answer

Your answer