// SQL Server error reference

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
Error message
Operand data type %ls is invalid for %ls operator.

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() or AVG() over a bit column (bit isn't a numeric type for aggregation).
  • Aggregating or doing arithmetic on a numeric value stored as text.
  • Using operators on legacy text, ntext or image columns.

How to fix it

  1. Cast to a numeric type before aggregating.

    SELECT SUM(CAST(IsActive AS int)) AS active_count FROM dbo.Users;
  2. Convert text columns holding numbers to a numeric type, or convert with TRY_CONVERT inside the aggregate.

    SELECT AVG(TRY_CONVERT(decimal(18, 2), Amount)) FROM staging.Payments;
  3. Replace text and ntext columns with varchar(max) and nvarchar(max).

Reproduce it safely

SELECT SUM(b) FROM (VALUES (CAST(1 AS bit))) AS t(b);  -- Msg 8117

Related errors

More data conversion and truncation errors

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.

// fewer errors, faster fixes

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.