T-SQL failed converting varchar to int

Question

I need to add days to date

select Name, Surname, Days, getdate(), 
CONVERT(VARCHAR(11),DATEADD(s,CONVERT(INT, Days) ), getdate()),101 )
from myTAble

but i get error

Conversion failed when converting the varchar value '20' to data type int.

any idea?

Can you create reproduceable examplе? Obviously, select convert(int, '20') will work as intended without such an error. So problem is somewhere in your data - or maybe error message you've provided is not fully relevant... Have you copy-pasted it exactly? Is there a chance that it is really 2O (capiltal letter O) instead of 20 in your data? — Andrey Korneyev
– Andrey Korneyev, Commented Feb 3, 2017 at 11:20
"SQL" is a query language, not a DBMS product. But I assume you do mean "SQL Server" — user330315
– user330315, Commented Feb 3, 2017 at 11:28

cloudsafe · Accepted Answer · 2017-02-03 11:55:27Z

4

Brackets mixed up:

select Name, Surname, Days, getdate(), 
CONVERT(VARCHAR(11),DATEADD(s,CONVERT(INT, days), getdate()),101) 
from myTAble

answered Feb 3, 2017 at 11:55

cloudsafe

2,5241 gold badge11 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jean · Accepted Answer · 2017-02-03 12:46:18Z

First your parentesis are incorrect but that's not likely the issue here. I created a verifiable example:

create table MyTable
(
 Name varchar(100)
,Surname varchar(100)
,[Days] varchar(100)
)

GO

insert into MyTable(Name,Surname,[Days])
values ('John', 'Doo', '20')
,('Stack', 'Overflow', char(178) + char(176))
GO

And now your query (with correct parentesis)

select Name, Surname, Days, getdate(), 
CONVERT(VARCHAR(11),DATEADD(s,CONVERT(INT, Days ), getdate()),101 )
from myTAble

and I managed to reproduce your problem (somewhat)

Conversion failed when converting the varchar value '²°' to data type int.

The issue here is you got a 20 is not the correct caracteres, maybe you got a collation problem.

In the example above I used a real problem I got a time ago. In the app is shows 20 but in reallity in the database we got non standart caracteres.

In the example below some special caractres can look like numeric ones

--° #176 ~ 0   --¹ #185 ~ 1   --² #178 ~ 2   --³ #179 ~ 3    
select char(178) + char(176)

You can try to check if the data is numeric

select *, isnumeric([Days]) from MyTable

In my case we sanitized the data.

Collectives™ on Stack Overflow

T-SQL failed converting varchar to int

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related