SQL Server Error 8116

Error number

8116

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Data Type & Conversion

Last reviewed

24 October 2026

What it means

Error 8116 means a built-in function received an argument whose data type it does not accept in that position, such as an int passed to SUBSTRING or a varchar passed as the number in DATEADD.

Each built-in function declares which types each argument may have. Some functions convert a wrong type for you (LEFT and LEN accept an int and treat it as text), others refuse it. When they refuse, the batch fails at compile time with 8116 at severity 16. The message tells you exactly where to look: the type you passed, the argument position counted from 1, and the function name in lower case. argument 2 of dateadd is the number of units, not the date.

Because the check is on the declared type, it fails even when every value would convert, for example a varchar column that only holds digits. That separates 8116 from run-time conversion errors such as 245. Two neighbours are easy to confuse: a quoted date part like DATEADD('day', ...) raises 1023 (invalid parameter), and aggregates such as SUM over a varchar raise 8117 (operand type invalid for the operator).

Common causes

• SUBSTRING applied to a numeric column, such as an order number or a date key stored as int.
• SUBSTRING on a date or uniqueidentifier instead of formatting it first.
• DATEADD whose number argument comes from a varchar column or a string parameter.
• FORMAT or EOMONTH given a date that is stored as text or as an int like 20261023.
• LEN or other string functions applied to an xml column.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT c.name AS column_name, t.name AS data_type, c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.Plans')
ORDER BY c.column_id;

SELECT id, term_days
FROM dbo.Plans
WHERE term_days IS NOT NULL
  AND TRY_CAST(term_days AS int) IS NULL;

EXEC sp_describe_first_result_set
     N'SELECT DATEADD(day, TRY_CAST(term_days AS int), start_date) AS ends_on FROM dbo.Plans;';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT SUBSTRING(CAST(order_no AS varchar(10)), 1, 4) AS year_part
FROM dbo.Orders;

SELECT DATEADD(day, TRY_CAST(term_days AS int), start_date) AS ends_on
FROM dbo.Plans;

SELECT order_no / 10000 AS year_part   -- 20261001 -> 2026
FROM dbo.Orders;

SELECT FORMAT(TRY_CONVERT(date, order_date), 'dd/MM/yyyy') AS shown
FROM dbo.Imports;

ALTER TABLE dbo.Plans ALTER COLUMN term_days int NULL;

Examples

SUBSTRING on an integer column

CREATE TABLE dbo.Orders (order_no INT PRIMARY KEY, total DECIMAL(10,2));

SELECT SUBSTRING(order_no, 1, 4) AS year_part  -- order_no is an int
FROM dbo.Orders;

Msg 8116, Level 16, State 1, Line 3
Argument data type int is invalid for argument 1 of substring function.

DATEADD with a number stored as text

CREATE TABLE dbo.Plans (id INT PRIMARY KEY, term_days VARCHAR(5), start_date DATE);

SELECT DATEADD(day, term_days, start_date) AS ends_on  -- term_days is varchar
FROM dbo.Plans;

Msg 8116, Level 16, State 1, Line 3
Argument data type varchar is invalid for argument 2 of dateadd function.

FORMAT on a date kept in a varchar column

CREATE TABLE dbo.Imports (id INT PRIMARY KEY, order_date VARCHAR(10));

SELECT FORMAT(order_date, 'dd/MM/yyyy') AS shown
FROM dbo.Imports;

Msg 8116, Level 16, State 1, Line 3
Argument data type varchar is invalid for argument 1 of format function.

How to prevent it

Store numbers, dates and identifiers in their own types and keep text functions for text. When a value really must be handled as text, convert it once, explicitly, at the point of use, rather than relying on each function to do it.

Check a function's argument types in its reference before using it on a new column type; the SUBSTRING and DATEADD pages in the functions library list them with examples.

Common questions

Learn more on CodeWithSQL

• SUBSTRING()
• DATEADD()
• CAST()

Official documentation

• SUBSTRING (Transact-SQL)
• DATEADD (Transact-SQL)
• FORMAT (Transact-SQL)
• Database Engine events and errors 8000 to 8999

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.