|
|
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]
|