| 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 3
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]
Solution/Workaround:
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 Topics:
|
|
|