Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Tutorial - UPDATE Statement 101
Error Messages
Home > SQL Server Error Messages > Msg 256 - The data type int is invalid for the substring function.  Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
SQL Server Error Messages - Msg 256 - The data type int is invalid for the substring function.  Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

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]
Related Articles :