Solved

t-sql decimal symbol error

Posted on 2009-04-06
2
400 Views
Last Modified: 2012-05-06
Hello Experts,
I have checked my regional settings and decimal seperator is (.)
But there are many records that consists decimal separator (,) in my database table. How can I format this rows as (.)
I mean when I try to run
select SUM(AccInvQty) from ItemDetails
then I get the wrong result. Because number format is wrong
Please help
0
Comment
Question by:innocent1973
2 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 24075510
Hi,

Is AccInvQty a charfield? Then you could use select sum(cast(replace(AccInvQty,',','.') as decimal)) from ItemDetails. Otherwise can you show us your error?

/peter
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24075691
1) solve the problem of . or , : you have to check your application that is generating the rows. if the application is using the system settings then if one of the systems is configured to use , then you will get this. if you are using .Net applicaiton to access the DB then use the culterinfo for hadling the . or , issues
2) correcting the existing data :
update ItemDetails set AccInvQty=replace(AccInvQty,',','.')
3)use the query given by pivar if you don;y want to correct the DB
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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