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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

Thanks Dear For Your Help