How to find which field is giving data type error in QUERY

I have the query report for user but have some error message display:

Error converting data type nvarchar to bigint

only one message.

How to find which field is giving data type error in query? Anyone can give advice? Many thanks!

Answer

As you have not provided data, it looks like you have NVARCHAR datatype, which is not implicitly convertable to Bigint

SELECT CAST(N’287888′ AS BIGINT) — Success
SELECT CAST(N’ABC123′ AS BIGINT) — Failure

See which column is failing and accordingly fix it.

You can either only load the proper values:

SELECT * FROM Table WHERE TRY_CAST(ErrorField AS BIGINT) IS NOT NULL

Or, You can load them as NULL(provided the target column allows NULL)

SELECT TRY_CAST(ErrorField AS BIGINT) FROM Table