Converting a varchar value into an int fails when the value includes a decimal point to prevent loss of data.

If you convert to a decimal or float value first, then convert to int, the conversion works.

Either example below will return 7082:

SELECT CONVERT(int, CONVERT(decimal(12,7), '7082.7758172'));
SELECT CAST(CAST('7082.7758172' as float) as int);

Be aware that converting to a float value may result, in rare circumstances, in a loss of precision. I would tend towards using a decimal value, however you'll need to specify precision and scale values that make sense for the varchar data you're converting.

You can use the decimal data type and specify the precision to state how many digits are after the decimal point. So you could use decimal(28,20) for example, which would hold 28 digits with 20 of them after the decimal point.

Here's a SQL Fiddle, showing your data in decimal format.

Fiddle sample:

create table Table1(MyValues varchar(100))

insert into Table1(MyValues)
values                    
('-28.851540616246499'),
('-22.857142857142858'),
('-26.923076923076923'),
('76.19047619047619')

So the values are held as varchar in this table, but you can cast it to decimal as long as they are all valid values, like so:

select cast(MyValues as decimal(28,20)) as DecimalValues 
from table1

Your Sample

Looking at your sample update statement, you wouldn't be able to convert the values from varchar to a numeric type and insert them back in to the same column, as the column is of type varchar. You would be better off adding a new column with a numeric data type and updating that.

So if you had 2 columns:

create table Table1(MyValues varchar(100), DecimalValues decimal(28,20))

You could do the below to update the numeric column with the nvarchar values that have been cast to decimal:

update Table1
set DecimalValues = cast(MyValues as decimal(28,20))

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

• try_convert(datatype,val)
• try_cast(val as datatype)
• try_parse(val as datatype [using culture])

So if you want to change the way a view is returning the value you would use something like:

alter view dbo.myView as 
  select
    ....
    , store_nbr = try_cast(dbo.customer.store_nbr as int)
    ....

There is by default function in SQL Server ISNUMERIC() so, first of all Check your data value by that function,

Select ISNUMERIC(DATA)

Whole query is written as below,

SELECT CASE WHEN ISNUMERIC(data)=1 THEN CAST(data as decimal(18,2))
            ELSE NULL END as tData FROM DataTable 

As per your question,first we have to convert with numeric with using case,which satisfies your first condition,another thing if the value is String than convert as NULL. In Above query both the condition has been taken care.

EDIT : If you are using SQL SERVER 2012 or higher version then use TRY_PARSE(), then there will be no need to worry about using CASE too...

I have tried this,

SELECT TRY_PARSE('63.36' as decimal(18,2)) got result 63.36

and

SELECT TRY_PARSE('.' as decimal(18,2)) got result NULL

I tried a lot of things to fix it, and no success.

I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.

I see that inside it has only blanks and numbers

When I try to CAST( MASTER_BOL_NUMBER as numeric)

I am getting an error “Error converting data type varchar to numeric.”

I tried also smth like that

CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)

and this

CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)

Also no success, I really don’t understand why I am getting this error because usually CAST or CONVERT fix this data type issues.

Does someone else know what may help but for those functions?

A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'  

The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.

i think it should be

select convert(varchar(10),StandardCost) +'S' from DimProduct where ProductKey = 212

or

select cast(StandardCost as varchar(10)) + 'S' from DimProduct where ProductKey = 212