SQL Server Performance Forum – Threads Archive

Conversion Problem

I’m running this query: select Ins_id,Ins_Company,Sgroup,trx_sec_ins_id,trx_pat_id,trx_date_from,round(convert(numeric(12,2),newsecondarybalance),3)as NewSecondaryBalance
from YearlyTransactions right outer join viewinsurance on (Yearlytransactions.trx_sec_ins_id=viewinsurance.ins_id) where viewinsurance.ins_id is not null
and yearlytransactions.trx_sec_ins_id is not null the field NewSecondaryBalance is a float and I need convert to numeric and after tht query run I got this error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ‘01.0’ to a column of data type int.
Warning: Null value is eliminated by an aggregate or other SET operation. any help on this will be appreciated
thanks

http://www.sqlservercentral.com/columnists/mcoles/gotchasqlaggregatefunctionsandnull.asp fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I find out this: select cast(newsecondarybalance as numeric(12,2)) from YearlyTransactions same error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ‘01.0’ to a column of data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.
How do I fix it?

This … declare @float float, @num12 as numeric(12, 2) set @float = ‘01.0’
set @num12 = cast(@float as numeric(12,2)) … works fine on my installation. Is the newsecondarybalance column really of the float data type, or is it varchar? If it is varchar, check whether the decimal in the string is by any chance different from the decimal separator on the Regional Settings of the computer that is running SQL Server. And then this …
set @float = ‘01,0’ … raises an error: "Error converting data type varchar to float."

still not working,
the field it’s varchar and I need to convert to numeric ex: select cast(newsecondarybalance as decimal(12,2)) from YearlyTransactions not working, any ideas?

That’s what I suspected. What happens if you run this, connected to the same server: — period as decimal separator
select cast(‘01.0’ as decimal(12,2)) — comma as decimal separator
select cast(‘01,0’ as decimal(12,2)) Either one should give the same error, and it should be related to the decimal separator on the computer running SQL Server.

the first one with "PERIOD" works fine and give me 1.00
the second one says cannot convert varchar to numeric

Well, that’s exactly what I have been telling you: your NewSecondaryBalance column, which is varchar, contains entries which have a comma where you are expecting a period. SQL Server will not accept a comma as the decimal separator if Windows says it must be a period, so quite literally it cannot convert the string value into a numeric value. Wherever the information on that column is coming from, they probably have a Windows installation that says the decimal separator is a comma. This is quite common outside of the USA, much like those funny date formats.

]]>