Search code examples
azure-data-factoryexpressionazure-mapping-data-flow

Converting datediff to Azure Mapping Data flows expression language

I need to convert this sql snippet to Azure Data Flow expression language:

"case when Trans_StatusId <> 1 then 0 else datediff(day, AppliedDate, getdate()) end"

I tried "iif(Trans_StatusId != 1, 0, subDays(currentTimestamp(), toDate(AppliedDate)))"

but I am getting the error "subDays' expects 'integral' type of argument"

Converting AppliedDate to integer may be expected but it's not logical to convert a timestamp to and int.

Solution

  • The subDays() function returns the date value by subtracting the number of days from the date in the first argument. Therefore, instead of using the subDays() function, you can follow the below approach. Take the currentDate() function to get the current date and the toDate function to convert "AppliedDate" to a date. Then, you can subtract the two dates to get the difference in days.

    Below is the ADF data flow expression to achieve the required value.

    iif(Trans_StatusId != 1, 0, datediff(currentDate(), toDate(AppliedDate)))

    Source data preview:

    Derived column Transformation:

    The equivalent expression of the given SQL code is provided in the derived column transformation. Below is the output.

    gif1