** 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.,},],'
CodePudding user response:
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
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.