How to use current datetime variable dynamically in ADF

Question

I have set a variable as 'currentdate' with string datatype and given the following default value

In the copy data action, I wrote the below sequel query

@concat('
SELECT 
    *
FROM 
   [',variables('schema'),'].[',if(variables('custom_table_list_flag'),item(),item().name),'] 
WHERE 
    UPDATE_DATE >  DATEADD(DAY,-1,CAST(''[',variables('currentdate'),']'' AS DATE))
    AND UPDATE_DATE <= CAST(''[',variables('currentdate'),']'' AS DATE);')

however ADF is returning the following error

Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: '/Parent/UAT'.,Source=mscorlib,''Type=System.Data.SqlClient.SqlException,Message=Conversion failed when converting date and/or time from character string.,Source=.Net SqlClient Data Provider,SqlErrorNumber=241,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=241,State=1,Message=Conversion failed when converting date and/or time from character string.,},],'

Answer 1

When there is an attempt to convert a string into a date or time value, but the string does not represent a valid date or time, the above error will occur. You can use the following expression:

DATEADD(DAY, -1, CAST(''', variables('currentdate'), ''' AS DATE))'

Output:

CAST(''', variables('currentdate'),''' AS DATE

Output:

Instead of the expression below:

DATEADD(DAY,-1,CAST(''[',variables('currentdate'),']'' AS DATE)
CAST(''[',variables('currentdate'),']'' AS DATE);')

The currentdate variable has a value of @formatDateTime(utcnow()). You will be able to run the query successfully without any error.