Argument data type int is invalid for argument 1 of substring function.

Shahab a 241 Reputation points
2022-10-03T09:10:07.067+00:00

Hi All
I Have Created A Table

CREATE TABLE [dbo].[Test](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	[YearsRef] [int] NOT NULL,  
	[Descrpt] [nvarchar](50) NOT NULL,  
	[Years] [int] NULL,  
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  

And Insert Records...

INSERT INTO [dbo].[Test]   values(202225,'Test1',null);  
INSERT INTO [dbo].[Test]   values(202229,'Test2',null);  
  

Now I Want Update The Table By This Code....

update [dbo].[Test]  
set Years=SUBSTRING(YearsRef,1,4)  

But I see this Error

Argument data type int is invalid for argument 1 of substring function.  

How To solve it.
Please Help Me
Thanks All

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2022-10-03T09:23:17.17+00:00

    Hi @Shahab a
    Try this:

     update [dbo].[Test]  
     set Years=SUBSTRING(cast(YearsRef as varchar(10)),1,4)  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Sreeju Nair 11,621 Reputation points
    2022-10-03T09:32:35.463+00:00

    The SQL Server substring function expect a string, so you need to cast or convert int to string. see a working example. Adjust the varchar(6) to the appropriate number if you change your data in the YearsRef field.

    update [dbo].[Test] set Years=SUBSTRING(cast(YearsRef as varchar(6)),1,4)  
    

    Refer: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

    Hope this helps

    0 comments No comments

  2. Shahab a 241 Reputation points
    2022-10-03T09:32:46.753+00:00

    Thanks Dear For Your Help

    0 comments No comments