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.

No - there is no workaround. Change the datatype of the column or change the value you are trying to insert. — SMor, Apr 11 '20 at 23:43
What "work around" would you imagine there is to insert a value that is *too large* into a data type? Just like you can't store more than 10 cahracters in a `varchar(10)`, you can't store a number greater than `255` in a `tinyint`. Use a data type appropriate to the data you need to store. Seems like you want either a `smallint` or a `decimal(4,0)`, which are 2 and 5 bytes in size respectively. — Thom A, Apr 12 '20 at 00:07
You can just store 20 and add 2000 to it everytime. But your application will cause the downfall of civilisation as we know it in the year 3000. — Nick.Mc, Apr 12 '20 at 01:09
I'm voting to close this question as off-topic because the request to store the value 2020 in a column with a datatype that only accepts 0-255 is nonsensical and insufficient information about the background has been supplied to know if there is a workaround. — Martin Smith, Apr 12 '20 at 13:07

score 0 · Accepted Answer · edited Apr 27 '20 at 22:50

0

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:

Without changing the datatype, and
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))

edited Apr 27 '20 at 22:50

Dale K

25,246
15
42
71

answered Apr 12 '20 at 23:31

LV6001

17
4

A simple expression would be SELECT CAST(FORMAT(GETDATE(), 'yy') AS TINYINT); – Rad Apr 12 '20 at 23:42

score -1 · Answer 2 · edited Apr 27 '20 at 22:50

-1

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;

edited Apr 27 '20 at 22:50

Dale K

25,246
15
42
71

answered Apr 12 '20 at 01:36

Obeth Ortiz

1

Small int range is : -2^15 (-32,768) to 2^15-1 (32,767) - your estimate is true – Amirhossein Apr 12 '20 at 07:33

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

2 Answers2