CODEWITHSUNDEEP
azure-sql-databaseazure-data-factory
Kate
Kate

Reputation: 53

ADF -pass SQL query in source with single quotes in date column

I am trying to pass parameters in dynamic pipeline in source query inside data flow. Tablename and Column name is dynamic and will be passed through parameters. But i got an error while executing the below ADF query:

ADF Query: concat('SELECT ', $ValidationColumn, ' FROM ', $ValidationTable,' WHERE audit_datetime >= ', replace($StartAuditDateTime,'T',''),' AND audit_datetime <= ' ,replace($EndAuditDateTime,'T','') )

Expected SQL query: Select ID from Master where audit_datetime >= '2020-07-23 18:47:20.5666' AND audit_datetime <= '2020-07-24 01:47:20.5456'

Upvotes: 2

Views: 2432

Answers (3)

Oliver Nilsen
Oliver Nilsen

Reputation: 1283

Use this SQL syntax in Expression builder in ADF Data Flow where you need to use singe quotes

"SELECT * FROM myTable WHERE someColumn = 'Y'"

Upvotes: 0

Kranthi Pakala
Kranthi Pakala

Reputation: 1422

Please try below expression to form your query in your ADF Mapping dataflow expression builder.

"SELECT '{$ValidationColumn}' FROM '{$ValidationTable}' WHERE audit_datetime >= '{$StartAuditDateTime}' AND audit_datetime <= '{$EndAuditDateTime}'"

This will result in below QUERY formation:

SELECT ID FROM Master WHERE audit_datetime >= '2020-07-23 18:47:20.5666' AND audit_datetime <= '2020-07-24 01:47:20.5456'

Upvotes: 1

DraganB
DraganB

Reputation: 1138

I am assuming you want to escape single quote to get '{yourDateValue}' format, you can do that like this ''''

example:

@{concat('select * from [yourtable] WHERE [yourColumn] >=','''',{yourexpression},'''')}

Upvotes: 0

Related Questions