How to convert an azure data factory string type variable into datetime format

Question

** I had a string type variable in azure data factory which is storing datetime format from a lookup activity** but after that i need to compare that value inside the variable with a datetime. how can i convert it into datetime format

i tried this but i am getting an error i will post the code and error below

varible--string(activity('Lookup1').output.value[1].CREATED_DATE) variable i created which converts datetime into string variable

query-select * from sampletable where modified_date >= formatDateTime(variables('createddate'),"o")``` this is the code i tried for comparing and to convert it into datetime format

ERROR Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: ''variables' is not a recognized built-in function name.',Source=,''Type=System.Data.SqlClient.SqlException,Message='variables' is not a recognized built-in function name.,Source=.Net SqlClient Data Provider,SqlErrorNumber=195,Class=15,ErrorCode=-2146232060,State=10,Errors=[{Class=15,Number=195,State=10,Message='variables' is not a recognized built-in function name.,},],'

Answer 1

You can try as per the below sample

@{concat('SELECT TOP (10) * FROM [SalesLT].[Customer] WHERE ModifiedDate <=', formatDateTime(variables('createddate'),'yyyy-MM-dd'))}

Equivalent to:

SELECT TOP (10) * FROM [SalesLT].[Customer] WHERE ModifiedDate <=2021-10-27 

See official doc: Functions in expressions

---

But If you try as per default format 'o' in formatDateTime() function

@{concat('SELECT TOP (10) * FROM [SalesLT].[Customer] WHERE ModifiedDate <=', formatDateTime(variables('createddate'),'o'))}

You might see the below error:

Try to refer formatDateTime and generate a query sutable for datetime format in your database.