blacklord
asked on
Decimal Seperator Problem
Hi... I have a problem with the decimal seperator in mysql while I am trying to insert or update data which has atleast 2 decimal places.
In Turkey, defoult currency format is like that 123,456.98 .... But in europe the default currency format is like that 123.345,65 ... So whenever I try to insert 34,12 I only see the 34.00. Nothing more... My field type is Double(10,2). I am able to insert 34.12 If I am using Mysql Control Center directly from sql query. Is it possible change the defoult mysql locale setting which is suitable to Turkish type?
In Turkey, defoult currency format is like that 123,456.98 .... But in europe the default currency format is like that 123.345,65 ... So whenever I try to insert 34,12 I only see the 34.00. Nothing more... My field type is Double(10,2). I am able to insert 34.12 If I am using Mysql Control Center directly from sql query. Is it possible change the defoult mysql locale setting which is suitable to Turkish type?
ASKER
Thanks for the answer.. But It still accept 12.45 rather than 12,45. This is the problem that I am trying to solve. My db should accept , as decimal seperator.
MySql will not accept decimal as seperator. I'm afraid you'll have to do the conversion on your front end. Their is no currency type as in MSSQL, Oracle, PostgreSql or even MS Access. Comma's are viewed as the field seperator in mysql and that's about it.
Your choices are convert on the front end to decimal, or do some programming on the MySql backend for a custom User Defined Function (search mysql udf) to convert a currency string to a decimal number. You would have to wrap all "money" fields with a euro_to_decimal('12,45') and then use decimal_to_euro(12.45) to get it back.
As useful as it sounds, I see know of no such utility that exists right now. Here is a list of UDF.
http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/
Even MySql v5 in development has no plans posted to add a money data type.
Possibly we can help with the front end issue, what are you working in?
Your choices are convert on the front end to decimal, or do some programming on the MySql backend for a custom User Defined Function (search mysql udf) to convert a currency string to a decimal number. You would have to wrap all "money" fields with a euro_to_decimal('12,45') and then use decimal_to_euro(12.45) to get it back.
As useful as it sounds, I see know of no such utility that exists right now. Here is a list of UDF.
http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/
Even MySql v5 in development has no plans posted to add a money data type.
Possibly we can help with the front end issue, what are you working in?
ASKER
Thanks for the answer petoskey-001. I understand your point. The important point is not to store decimal numbers with ",". Later then when I use Crytal report, that reporting program generates much more problems because of my table field type.
"Your choices are convert on the front end to decimal, or do some programming on the MySql backend for a custom User Defined Function (search mysql udf) to convert a currency string to a decimal number. You would have to wrap all "money" fields with a euro_to_decimal('12,45') and then use decimal_to_euro(12.45) to get it back."
Can you give me some example about it?
"Your choices are convert on the front end to decimal, or do some programming on the MySql backend for a custom User Defined Function (search mysql udf) to convert a currency string to a decimal number. You would have to wrap all "money" fields with a euro_to_decimal('12,45') and then use decimal_to_euro(12.45) to get it back."
Can you give me some example about it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help petoskey...
you can use string for update,calculate,insert money(123,456.98 ) format and you can modify your Currency using replace and cast function.
Example :
SELECT sum(CAST((replace(carihare
1-Replace , to .
2-convert currency to signed value using CAST function
3- convert signed value to float using *100 and / 100
4- Sum of column
And i find second way,
1-Create your money column double(14,2)
2-And use : Replace(Format(sayi.ValueD ouble, "###0.00"), ",", ".") during using insert and update query.
__________________________ __________ __________ __________ __________ __________ _______
example Update::
updateCmd = "UPDATE aidatcarihareket SET tutar='" & Replace(Format(sayi.ValueD ouble, "###0.00"), ",", ".") & "' WHERE SiraNo = 1" ' ,update_by='" & adminNo & "' , update_date='" + yaratilmatarihi.ToString(" yyyyMMddhh mmss") + "' WHERE SiraNo = '" & SiraNo & "'"
if You use my second way you dont need any format and string operation when you want to use select . :)
__________________________ __________ __________ __________ __________ __________ _______
Example Select ::
updateCmd = "select tutar from aidatcarihareket WHERE SiraNo = 1" ' ,update_by='" & adminNo & "' , update_date='" + yaratilmatarihi.ToString(" yyyyMMddhh mmss") + "' WHERE SiraNo = '" & SiraNo & "'"
Dim myCommand As New OdbcCommand(updateCmd, myConnection)
myConnection.Open()
Dim deger As Double
deger = myCommand.ExecuteScalar()
========================== ========== ========== ========== ==
Erkan SALDIR 16 - January 2005
1-Create your money column double(14,2)
2-And use : Replace(Format(sayi.ValueD
__________________________
example Update::
updateCmd = "UPDATE aidatcarihareket SET tutar='" & Replace(Format(sayi.ValueD
if You use my second way you dont need any format and string operation when you want to use select . :)
__________________________
Example Select ::
updateCmd = "select tutar from aidatcarihareket WHERE SiraNo = 1" ' ,update_by='" & adminNo & "' , update_date='" + yaratilmatarihi.ToString("
Dim myCommand As New OdbcCommand(updateCmd, myConnection)
myConnection.Open()
Dim deger As Double
deger = myCommand.ExecuteScalar()
==========================
Erkan SALDIR 16 - January 2005
DECIMAL( length , decimals ) [UNSIGNED] [ZEROFILL]
From http://dev.mysql.com/doc/mysql/en/Choosing_types.html:
Accurate representation of monetary values is a common problem. In MySQL, you should use the DECIMAL type. This is stored as a string, so no loss of accuracy should occur. (Calculations on DECIMAL values may still be done using double-precision operations, however.) If accuracy is not too important, the DOUBLE type may also be good enough.