How to format an activity output as YYYY-MM-DD hh:mm:ss in Azure data factory-CodePudding

In my ADF pipeline I am trying to convert an output from my lookup activity to be in YYYY-MM-DD hh:mm:ss date format within the source query of a copy activity. The current output from my lookup activity is in YYYY-MM-DDThh:mm:ss format and I need to remove the 'T'.

I have tried using the dynamic content and formatDateTime functions but am having problems with the syntax. I am also using an SQL query to retrieve only the relevant data. The below is what I am using as an input in the dynamic content query. I am able to get this to work, but I need to change '03/15/2018 12:00:00' to refer to the output of my lookup activity named LookupNewWaterMarkActivity.

SELECT * 
FROM tableName
WHERE updatedDate > 
'@{formatDateTime('03/15/2018 12:00:00', 'yyyy-MM-dd HH:mm:ss')}'

I have tried the below, but get the following error message: 'cannot fit package::output:any & { count, value } into the function parameter string. (6)'

SELECT * 
FROM tableName
WHERE updatedDate > 
'@{formatDateTime(activity('LookupNewWaterMarkActivity').output, 'yyyy-MM-dd HH:mm:ss')}'

Does anyone know how I can format the output of my activity within an SQL query any other way?

Thanks

CodePudding user response：

Use the lookup activity output (activity('Lookup1').output.value[0].columnName) value in your expression with column name as shown below to refer the lookup activity output in later activities.

Lookup activity output:

Copy activity:

Expression:

@concat('SELECT * FROM tb2 WHERE date1 > ''',formatDateTime(activity('Lookup1').output.value[0].date1, 'yyyy-MM-dd HH:mm:ss'),'''')

CodePudding user response：

@NiharikaMoola-MT

Thanks for the response. I am getting the below error when running the below code.

@concat('SELECT * FROM tableName WHERE sys_updated_on_value > ''',formatDateTime(activity('LookupNewWaterMarkActivity').output.value[0].sys_updated_on_value, 'yyyy-MM-dd HH:mm:ss'),'''')

Error code: FailToResolveParametersInExploratoryController

Details The parameters and expression cannot be resolved for schema operations. Error Message: { "message": "ErrorCode=InvalidTemplate, ErrorMessage=The expression 'concat('SELECT * FROM tableName WHERE sys_updated_on_value > ''',formatDateTime(activity('LookupNewWaterMarkActivity').output.value[0].sys_updated_on_value, 'yyyy-MM-dd HH:mm:ss'),'''')\n\n' cannot be evaluated because property 'value' doesn't exist, available properties are 'value[0]'.." }