ADF SQL Query with single quotes around dates

Question

I'm trying to do a simple pull from oracle based on a date. Eventually, I need this query to be dynamic hence using @concat() function in ADF. For simplicity sake I want to run the below oracle query in ADF with concat.

Since oracle requires single quotes I'm trying to escape it by using two single quotes.

oracle query:

select * from schema.customer where updated > to_date('06/25/2019','MM/DD/YYYY')

here's my ADF text:

@concat('select * from iris.iris_customer where updated > to_date(','''06/25/2019''','''MM/DD/YYYY''',');')

I'm using to_date because I was getting the 'not a valid month' error and thought I could resolve it. Here's the error I'm getting:

{ "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22008] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01843: not a valid month,Source=msora28.dll,'", "failureType": "UserError", "target": "copy_to_adl" }

Answer 1

you should use '' for escaping single quote.

My Example: @{concat('DELETE FROM MyTable WHERE [ColumnDate] LIKE','''',pipeline().parameters.processYear,pipeline().parameters.processMonth,'%','''')}