7 Replies - 9373 Views - Last Post: 07 June 2010 - 09:18 AM Rate Topic: -----

#1 dulemk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 04-June 10

Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 09:54 AM

[QUOTE=dulemk;1945114]My problem is that I can't convert varchar value from my table to supported sum data type.

if I use this
SUM(CAST(TheColumn AS int))


I get this message:
Conversion failed when converting the varchar value '1.5' to data type int.
I know it is because int is not supporting comma.


If I CAST to numeric, decimal or float I get this:
Error converting data type varchar to float.

I know that varchar is not supported by SUM but if I try to change the data type in my table columns to decimal I have new problem.
The problem is that I cant insert decimal numbers like 1.5 because massage pops:
Error converting data type varchar to numeric.

But if I don't use decimal number and only work with natural numbers it is everything fine.

Here is the code for insert in to the table.

                value1 = double.Parse(this.textBox1.Text);
                value2 = double.Parse(this.comboBox3.Text);
                value3 = double.Parse(this.textBox11.Text);
                den = (value1 * value2) / 100 * value3;           

            double denn = den;




denn is used in the sqlcommand.

So what to use so I can sum decimal numbers with comma like 1,5 from my sql database.[/QUOTE]

Is This A Good Question/Topic? 0
  • +

Replies To: Convert problem varchar to supported sum sql statement

#2 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 12:12 PM

this worked fine for me..

SELECT SUM(CAST(Name as decimal(18, 1)))
FROM Components
WHERE ID in (3, 4)



The "Name" column is a varchar. I put a value of "1.5" and "2" into the Name field of the two records with ID of 3 and 4. It returned "3.5".

So it works fine for me in SQL 2008. I don't know about SQL 2005 or before.
Was This Post Helpful? 0
  • +
  • -

#3 dulemk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 04-June 10

Re: Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 01:20 PM

No it is not working.

I tried that.

it give me:
Error converting data type varchar to numeric.

In the table I can't set it to numeric as I said earlier


only this works:
SUM(CAST(MKDProvizija AS int))

if in the table all data is natural numbers.

So I need something that can sum, but can work with decimal numbers(ex:0,5) to.
Or some other approach...
Was This Post Helpful? 0
  • +
  • -

#4 demausdauth   User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 01:51 PM

Are you sure that all the data in the MKDProvizija column is able to be cast as a decimal (numeric)? It seems that if you have a alphabetic character, NULL, or blank then it will cause an issue when trying to do the cast.
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 01:55 PM

sounds like your SQL Server instance is setup for the US when it should be setup for a country that allows a "comma" as a decimal place. In the US, the decimal is a "period"( . ). I will only assume that a "decimal" in your country is a "comma" ( , ).

I believe you need to change the default language.
Was This Post Helpful? 1
  • +
  • -

#6 dulemk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 04-June 10

Re: Convert problem varchar to supported sum sql statement

Posted 04 June 2010 - 06:04 PM

View Postdemausdauth, on 04 June 2010 - 12:51 PM, said:

Are you sure that all the data in the MKDProvizija column is able to be cast as a decimal (numeric)? It seems that if you have a alphabetic character, NULL, or blank then it will cause an issue when trying to do the cast.

null is not the problem, the problem is that numeric or decimal is not supporting comma for me, or can't find anything that can convert from varchar that support sum and comma.

View Posteclipsed4utoo, on 04 June 2010 - 12:55 PM, said:

sounds like your SQL Server instance is setup for the US when it should be setup for a country that allows a "comma" as a decimal place. In the US, the decimal is a "period"( . ). I will only assume that a "decimal" in your country is a "comma" ( , ).

I believe you need to change the default language.


I tried both and it is not working.
Was This Post Helpful? 0
  • +
  • -

#7 dulemk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 04-June 10

Re: Convert problem varchar to supported sum sql statement

Posted 06 June 2010 - 11:35 AM

View Posteclipsed4utoo, on 04 June 2010 - 12:55 PM, said:

sounds like your SQL Server instance is setup for the US when it should be setup for a country that allows a "comma" as a decimal place. In the US, the decimal is a "period"( . ). I will only assume that a "decimal" in your country is a "comma" ( , ).

I believe you need to change the default language.



You are right.

This solve my problem:

UPDATE table SET MKDProvizija = REPLACE(MKDProvizija,',','.')


So after all the problem is whit the language.
The same problem I had with a datetime, it showed out of range.

I use sql express with visual studio 2008.
So is there a way to synchronize those two to be Europian style??
Lots of problems come out only because of that.
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Convert problem varchar to supported sum sql statement

Posted 07 June 2010 - 09:18 AM

you should be able to use this command to set the language of sql server.

http://msdn.microsof...y/ms174398.aspx
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1