SQL Server Error Messages - Msg 256¶

Error Message¶

Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function.
Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

Causes:¶

As the message suggests, this error occurs when using the SUBSTRING string function and the data type of the first parameter is INT.

To illustrate, the following script will generate the error:

DECLARE @YYYYMMDD  INT
SET @YYYYMMDD = 20060101
SELECT SUBSTRING(@YYYYMMDD, 1, 4) AS [Year]

Server: Msg 256, Level 16, State 1, Line 3
The data type int is invalid for the substring function.
Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

Solution / Work Around:¶

To avoid this error, always make sure that the data type of the first parameter that is passed to the SUBSTRING function is of char, varchar, nchar, nvarchar, binary or varbinary data type. If the data type is not any of these, you can use the CAST function to convert it to one of these data types.

DECLARE @YYYYMMDD  INT
SET @YYYYMMDD = 20060101
SELECT SUBSTRING(CAST(@YYYYMMDD AS VARCHAR(8)), 1, 4) AS [Year]