February 11, 2016 at 1:36 pm
I have a column with varchar data type but has numeric data in it
I know I can't use arithmetic operation for varchar
This is my query
select * from table1
where col1> 500.00
Msg 8115, Level 16, State 8, Line 38
Arithmetic overflow error converting varchar to data type numeric.
obviously it gives me this error.
what functions should I use to convert the data type?
I appreciate your help
Thanks
February 11, 2016 at 1:40 pm
Here you go:
https://msdn.microsoft.com/en-us/library/ms187928.aspx
If you are on SQL Server 2012 or better, look here as well: https://msdn.microsoft.com/en-us/library/hh974669.aspx
February 11, 2016 at 1:55 pm
Just a couple things to consider. Make sure you validate your data to make sure it's actually a number. i.e. (ISNUMERIC). Secondly, try to avoid using CONVERT in your WHERE clause as this can cause performance issues and prevent the use of indexes.
February 11, 2016 at 2:11 pm
yb751 (2/11/2016)
Just a couple things to consider. Make sure you validate your data to make sure it's actually a number. i.e. (ISNUMERIC). Secondly, try to avoid using CONVERT in your WHERE clause as this can cause performance issues and prevent the use of indexes.
Be careful with IsNumeric(). It can produce unexpected results (see here[/url] for why).
Possibly a better approach is to use TRY_CAST().
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 11, 2016 at 2:13 pm
SQLAddict01 (2/11/2016)
I have a column with varchar data type but has numeric data in itI know I can't use arithmetic operation for varchar
This is my query
select * from table1
where col1> 500.00
Msg 8115, Level 16, State 8, Line 38
Arithmetic overflow error converting varchar to data type numeric.
obviously it gives me this error.
what functions should I use to convert the data type?
I appreciate your help
Thanks
May I ask what was wrong with the solution provided yesterday?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply