INSERT statement - SQL server- Error -Nvarchar value ‘2020’ overflowed TINYINT column

Question

Trying to insert value of Year i.e. 2020 into column with dataype TINYINT which is not possible because TINYINT stores from 0-255. Is there a workaround/solution (using Convert/Cast or any other possible way ) if I want to store the value 2020 without using a larger integer column (i.e. without changing the datatype from TINYINT to INT, BIGINT etc. ) I’m using SQL SERVER Management Studio. Please help.

Answer 1

I understand that 2020 cannot be saved to tinyint and the datatype needs to be changed to int,bigint,varchar etc.

Further elaborating my question statement, I was required to enter Year somehow in TINYINT:

1. Without changing the datatype, and
2. By using inbuilt DATENAME and getdate() functions in SQL server.

So, I stored 20 as per above two requirements by using the below:

CONVERT(TINYINT,SUBSTRING(CAST(DATENAME(YEAR,GETDATE())AS CHAR),3,2))

Answer 2

alter yourTable alter column year varchar(4);

If you need as a number you need to use at least smallint (-32000 to 32000)

alter yourTable alter column year smallint;