February 5, 2009 at 10:19 am
Dear All,
I have a table with field varchar data type. This field can have int or alpha numeric characters in the table. In SQL 2000 it is not an issue but in SQL 2005 when we have queries to retrive the data from this table with out quotes for this field value it is failing with the following error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '5.0' to data type int.
We have used this in many places of our code. is there any way we can fix this with SQL Server configurations with out fixing the code. It fails for both 80 and 90 compatible modes in SQL Server 2005.
Regards,
Sri
February 5, 2009 at 10:23 am
'5.0' will not convert to an integer as it can not interpret the '.0' part, since INT can't handle decimals. Try using Numeric data type but this might mean changing your code.
Without chaing your code to handle this, you could run an update statement on the databse to remove the '.0'
February 5, 2009 at 10:29 am
Thanks Steve,
We have many other non-numeric values like this in the table, we can not run the update statement.
Regards,
Sri
February 5, 2009 at 10:34 am
Okay Thanks,
Without updating the code or updating the data then there is not really much that can be easily done..
February 5, 2009 at 11:55 am
cast the VARCHAR value to DECIMAL and then cast to INT.
DECLARE @t TABLE (data varchar(10))
INSERT INTO @t(data) SELECT 'abc'
INSERT INTO @t(data) SELECT '5'
INSERT INTO @t(data) SELECT '7.0'
INSERT INTO @t(data) SELECT 'A1'
SELECT
CAST(CAST(data AS DECIMAL) AS INT) AS val
FROM @t
WHERE ISNUMERIC(data) = 1
/*
val
-----------
5
7
*/
.
February 5, 2009 at 11:32 pm
Hi,
I have considered an example for your problem.
DECLARE @vchTemp VARCHAR(10)
SET @vchTemp = '2.6'
SELECT CAST(CAST(@vchTemp AS NUMERIC(18,4)) AS INT)
-- Output : 2
SELECT CAST(CAST(@vchTemp AS DECIMAL) AS INT)
-- Output : 3
You can use the proper conversion that you require.
Hope this might help you to solve the issue.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply