Azure ADF Error Arithmetic overflow error converting expression to data type int-CodePudding

I'm working with azure ADF and I'm having problems when I execute this query on azure ADF:

SELECT COUNT(*) AS c
FROM TABLE 
WHERE CONVERT(date, (FORMAT(DATEADD(second, CONVERT(bigint, TS) / 1000, '19700101'), 'yyyy-MM-dd'))) = CONVERT(Date, GETDATE())

The output is an error

Arithmetic overflow

but if I change the = to a >=, the query works and returns an output.

The TS is a UNIXTIMESTAMP like 1637680012264.

Using the combination of >= and < is not ok because I'm dealing with not contiguous days (I need to use WHERE TS IN (date1, date2, etc...)

Could someone help me? Thanks in advance

CodePudding user response：

Preferably, I would change the table to store datetime2 values instead of convoluted epoch junk.

But, assuming you can't fix the design...

To Larnu's point, you don't want to apply computations to the column, and you definitely don't want to apply FORMAT() to both sides because

ADF:

Using Lookup activity, getting the count of rows where TS column has today’s date. (I am using your code with a different table).

SELECT COUNT(*) AS c
FROM tb1 
WHERE CONVERT(date, (FORMAT(DATEADD(second, CONVERT(bigint, TS) / 1000, '19700101'), 'yyyy-MM-dd'))) = CONVERT(Date, GETDATE())

Output: