December 9, 2012 at 10:56 pm
i have a query :
select c.kode,cast(Right(c.kode,5) as integer) as NOSKM from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001'
order by cast(Right(c.kode,5) as integer)
the sql server give me: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '.0303' to data type int.
c.kode is varchar 15. and when i check the data with:
select c.kode from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001' and Right(c.kode,5)='.0303'
it give me 0 row of result which mean no '.0303' data to be converted.
what i have missed here?
thanks.
December 9, 2012 at 11:31 pm
saputra.budi (12/9/2012)
i have a query :select c.kode,cast(Right(c.kode,5) as integer) as NOSKM from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001'
order by cast(Right(c.kode,5) as integer)
the sql server give me: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '.0303' to data type int.
c.kode is varchar 15. and when i check the data with:
select c.kode from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001' and Right(c.kode,5)='.0303'
it give me 0 row of result which mean no '.0303' data to be converted.
what i have missed here?
thanks.
If you need to retain the decimal points why don't you use numeric/decimal or float data types instead of integers.
DECLARE @a VARCHAR(15) ='0.03035'
SELECT CONVERT(INT,@A) --Will fail
SELECT CONVERT(decimal (5,5),@A)
December 9, 2012 at 11:51 pm
its working using numeric conversion. thanks for the help.
but just want to know more about my problem.
well actually i dont need the decimal value, because in my code have no decimal related.
the code it self is my items id where in some case the format would be xxx.9x.xx.99999 and the other are 99999.9999
each data separated by '.' have its own meaning. and for my case right now i just want to get the last 5 char and convert it to number.
what wierd about it was, when i just do simple select on the code without any conversion thing, there is no data with '.0303' on its right side.
select c.kode from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001' and Right(c.kode,5)='.0303'
so how can the sql server say that it fail to convert '.0303' value to integer? or maybe the sql server convert all data in my table then show the result that match with my condition?
December 10, 2012 at 8:56 pm
saputra.budi (12/9/2012)
its working using numeric conversion. thanks for the help.but just want to know more about my problem.
well actually i dont need the decimal value, because in my code have no decimal related.
the code it self is my items id where in some case the format would be xxx.9x.xx.99999 and the other are 99999.9999
each data separated by '.' have its own meaning. and for my case right now i just want to get the last 5 char and convert it to number.
what wierd about it was, when i just do simple select on the code without any conversion thing, there is no data with '.0303' on its right side.
select c.kode from cachekomponen c, barang b
where c.kode=b.kode and b.proses='CBC' and c.owner='1A.0001' and Right(c.kode,5)='.0303'
so how can the sql server say that it fail to convert '.0303' value to integer? or maybe the sql server convert all data in my table then show the result that match with my condition?
The problem here boils down to your string data having decimal points which is not accepted in integer conversion. The conversion will fail even if a single record out of million does not meet the condition. If you do not need decimal points, search on BOL (Book Online) for help with string functions to help you split the strings and go forward from there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply