CODEWITHSUNDEEP
sqlsql-servert-sql
user9302406
user9302406

Reputation:

conversion varchar to int issues

The following query attempts to convert a varchar datatype to int -- but it's not working. Do you know why?

SELECT 
    CONVERT(INT, [BR]) AS dd,
    CAST([BR] AS INT) AS cc
FROM 
    [Seg].[dbo].[pro_ben]

I get the error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '3893.768' to data type int

Upvotes: 0

Views: 3684

Answers (4)

Seth Greenstein
Seth Greenstein

Reputation: 153

I would try 

Select Round([BR],0) as dd
FROM [Seg].[dbo].[pro_ben];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The value is not an integer. This leaves you with various options:

  • You could use try_convert() which would return NULL.
  • You can use an intermediate data type and then convert to the final data type, but you might lose precision (under some circumstances).
  • You could convert to a numeric() instead.

Conversion to a numeric ignores decimal points, but they do not cause an error:

SELECT convert(numeric(10,0), [BR]) as dd
FROM [Seg].[dbo].[pro_ben];

You can then convert to an int if you specifically need that type.

Upvotes: 1

GMB
GMB

Reputation: 222672

Obviously your string has a decimal part, hence you cannot cast it directly to an integer.

A solution would be to first CAST to a floating point number, and then to an integer, like:

SELECT CAST( CAST([BR] AS FLOAT) AS INT) AS CC FROM [Seg].[dbo].[pro_ben]

Demo on DB Fiddle:

SELECT CAST('3893.768' AS FLOAT)

| (No column name) |
| ---------------: |
|         3893.768 |

SELECT CAST( CAST('3893.768' AS FLOAT) AS INT)

| (No column name) |
| ---------------: |
|             3893 |

Upvotes: 3

user9302406
user9302406

Reputation:

mmmm it appears once I change data type to float, it works.

Upvotes: -1

Related Questions