Anyone know what where this issue is with this conversion?

1.) Set Variable Output is "1/2/2025" enter image description here

I still receive this error with the below script..."Operand type clash: int is incompatible with date"

enter image description here

Anyone know what where this issue is with this conversion?

1.) Set Variable Output is "1/2/2025" enter image description here

I still receive this error with the below script..."Operand type clash: int is incompatible with date"

enter image description here

• sql
• azure-data-factory

3 Answers 3

Resolved "Operand type clash: int is incompatible with date" by using sql CONVERT function.

convert(datetime,@{variables('date_value')});

Get the "Operand type clash: int is incompatible with date" utilizing a set variable in ADF with SQL script

Your Azure Data Factory (ADF) pipeline is encountering a date conversion error because the Lookup activity is fetching a date in an incorrect format ('1/2/2025'), which may not match SQL Server's expected YYYY-MM-DD format when inserting into the database

Follow the below steps which I have tried with:

Step:1
Below is the sample query which I have used in the Lookup Activity to fetch data from Azure SQL DB.

Step:2
A new pipeline variable named varDate was created and linked Lookup to Set Variable.

Step:3
The following script activity contains an SQL query which successfully inserts data into the Azure SQL Database.

Using your existing pipeline activites this is probably the simplest approach.

1. Do your lookup exactly how you are

2. In your Set Variable Activity use this dynamic content expression:

@formatDateTime(activity('Lookup1').output.firstRow.xdate, 'yyyy-MM-dd')

3. Your Script Activity can be simplified to just this:

delete from dbo.temp_paycor_payroll
where [Check Date] = '@{variables('xdate')}'

This should do it! Cheers!