SQL Server 2008

Question

SELECT CAST('-1'AS TINYINT) 

Why this statement gives below error on SQL server R2 but not on SQL server 2016

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '-1' overflowed an INT1 column. Use a larger integer column.

Answer 1

These are all options...

select convert(smallint,-1) select convert(int,-1)

select convert(smallint,1-2) select convert(int,1-2)

Answer 2

Try this:

SELECT CAST(-1 AS smallint)

NOTE: The range of tinyint is 0 to 255.

For reference int, bigint, smallint, and tinyint (Transact-SQL) MSDN

Answer 3

Simply TINYINT's range is between 0-255. So you can not convert a negative number to TINYINT. This range and behavior is regardless of the SQL Server's version. You can try to convert your value to SMALLINT instead.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

Answer 4

To quote the documentation int, bigint, smallint, and tinyint (Transact-SQL):

Data type   Range                                                                       Storage
bigint      -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)    8 Bytes
int         -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                            4 Bytes
smallint    -2^15 (-32,768) to 2^15-1 (32,767)                                          2 Bytes
tinyint     0 to 255                                                                    1 Byte

As you can see, tinyint has a range of 0 to 255. -1 is outside of that range. Use a different datatype, for example, smallint.