CODEWITHSUNDEEP
azure-data-factory
Michael
Michael

Reputation: 511

Query by date in Azure Data Factory Pipeline

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

Upvotes: 2

Views: 4702

Answers (3)

Ravi kumar
Ravi kumar

Reputation: 171

I had the same issue I have sorted it out via the following method:

  1. Create a Set variable activity (Let's say the name - X)

enter image description here

@formatDateTime('2022-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')

  1. Write the following Code to the pipeline expression builder:

    @{concat('select * from Table where Column1=''','A',''' AND TO_CHAR(Date, ''', 'YYYY-MM-DD HH24:MI:SS',''') >= ''', variables('X'),'''')}

This will do the trick!

Upvotes: 0

Bets
Bets

Reputation: 21

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

Upvotes: 2

DraganB
DraganB

Reputation: 1138

Here is the possible solution for your problem.

enter image description hereIn 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)

Upvotes: 4

Related Questions