Query by date in Azure Data Factory Pipeline

Question

I want to use a query in a copy job for my source in an Azure Data Factory pipeline together with a date function - here is the dummy query:

SELECT * FROM public.report_campaign_leaflet WHERE day="{today - 1d}"

I´ve found some documentation about dynamic content and some other stuff but no information on how to use date functions directly in a sql query.

Maybe someone has a hint for me?

Thanks & best, Michael

Answer 1

Here is the possible solution for your problem.

In your copy activity, at the source side, you choose query in Use Query option, and then in the query box you write an expression

Here is the expression @concat('SELECT * FROM public.report_campaign_leaflet WHERE day=','"',formatDateTime(adddays(utcnow(),-1), 'yyyy-MM-dd'),'"')

formatDateTime function will just format the output of addDays(utcnow(),-1) into yyyy-MM-dd format

Again, you can have a parameter in your pipeline processDate for example, and to set this value from expression in trigger definition, and then just to call that parameter in the query. (suggestion)

Answer 2

You need to replace the double quote (") with two single quotes (''):

@concat('SELECT * FROM public.report_campaign_leaflet WHERE day=','''',formatDateTime(adddays(utcnow(),-1), 'yyyy-MM-dd'),'''')