SQL Server error 8117: Operand data type invalid for operator
An operator was applied to a type that doesn't support it, such as + or SUM on bit, or arithmetic on text, ntext or xml.
- Error number
8117- Severity
- 16
- Category
- Data conversion and truncation
What it means
Each operator works on certain data types. Error 8117 names the type and the operator that don't go together, for example Operand data type bit is invalid for sum operator or Operand data type varchar is invalid for avg operator when averaging a text column.
Common causes
SUM()orAVG()over abitcolumn (bit isn't a numeric type for aggregation).- Aggregating or doing arithmetic on a numeric value stored as text.
- Using operators on legacy
text,ntextorimagecolumns.
How to fix it
-
Cast to a numeric type before aggregating.
SELECT SUM(CAST(IsActive AS int)) AS active_count FROM dbo.Users;
-
Convert text columns holding numbers to a numeric type, or convert with
TRY_CONVERTinside the aggregate.SELECT AVG(TRY_CONVERT(decimal(18, 2), Amount)) FROM staging.Payments;
-
Replace
textandntextcolumns withvarchar(max)andnvarchar(max).
Reproduce it safely
SELECT SUM(b) FROM (VALUES (CAST(1 AS bit))) AS t(b); -- Msg 8117
Related errors
245Conversion failed converting a value8114Error converting data type42883PostgreSQL: function does not exist
More data conversion and truncation errors
8152String or binary data truncated2628Truncated in table, column241Conversion failed for date and/or time242Out-of-range date conversion8115Arithmetic overflow error converting512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.