sql-serverruntime-errorsql-inserttinyint

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

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.

Solution

  • 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))