CODEWITHSUNDEEP
sqlsql-servert-sqlintvarchar
dev
dev

Reputation: 181

T-SQL failed converting varchar to int

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?

Upvotes: 0

Views: 988

Answers (2)

jean
jean

Reputation: 4350

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.

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2506

Brackets mixed up:

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

Upvotes: 4

Related Questions