Solved

Decimal Seperator Problem

Posted on 2004-10-09
8
780 Views
Last Modified: 2008-01-16
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?
0
Comment
Question by:blacklord
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12268067
For complete accuracy with monetary values, you should use the Decimal datatype, and you should use your front-end to control the formatting to and from European standards.

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.
0
 

Author Comment

by:blacklord
ID: 12268219
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.
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12270804
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?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:blacklord
ID: 12271002
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?
0
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 250 total points
ID: 12271024
Well here's a FAQ
http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/faq.html

Here's a source forge project to download source code
http://mysql-udf.sourceforge.net/

And of course the official manual on the subject...
http://dev.mysql.com/doc/mysql/en/Adding_functions.html


0
 

Author Comment

by:blacklord
ID: 12274300
Thanks for your help petoskey...
0
 

Expert Comment

by:erkansaldir
ID: 13020837

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(carihareket.tutar,',','.')*100) as signed)/100) FROM carihareket  where Sirano=4

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
 
0
 

Expert Comment

by:erkansaldir
ID: 13054989
And  i find  second way,
1-Create your money column double(14,2)  
2-And use :  Replace(Format(sayi.ValueDouble, "###0.00"), ",", ".")   during using  insert and  update query.
___________________________________________________________________________________
example Update::
updateCmd = "UPDATE aidatcarihareket SET tutar='" & Replace(Format(sayi.ValueDouble, "###0.00"), ",", ".") & "' WHERE SiraNo = 1" ' ,update_by='" & adminNo & "' , update_date='" + yaratilmatarihi.ToString("yyyyMMddhhmmss") + "' 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("yyyyMMddhhmmss") + "' WHERE SiraNo = '" & SiraNo & "'"

            Dim myCommand As New OdbcCommand(updateCmd, myConnection)
            myConnection.Open()
            Dim deger As Double
            deger = myCommand.ExecuteScalar()

==========================================================
Erkan SALDIR 16 - January 2005
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question