Simple Round call results in Arithmetic overflow

Hi,


I'm a bit dazzled why this statement won't work in T-SQL : Select Round(9.990000, 0)

The error I get is : Arithmetic overflow error converting expression to data type numeric.

The result should be : 10

I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2), didn't find anything in BOL either

Select ROUND(19.99, 0) works just fine


Is this a bug or not? We develop accounting software and for us this is a dangerous bug.





Submitted via EggHeadCafe - Software Developer Portal of Choice 
Assemblies in Folder Debug Build Checker
http://www.eggheadcafe.com/tutorials/aspnet/d7de5fe1-6155-4e81-96e1-9806fd69d760/assemblies-in-folder-debug-build-checker.aspx
0
Sven
7/29/2010 1:56:12 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
1601 Views

Similar Articles

[PageSpeed] 25

"Sven Peeters" <sven.peeters@systemat.com> wrote in message 
news:201072995556sven.peeters@systemat.com...
> Hi,
>
>
> I'm a bit dazzled why this statement won't work in T-SQL : Select 
> Round(9.990000, 0)
>
> The error I get is : Arithmetic overflow error converting expression to 
> data type numeric.
>
> The result should be : 10
>
> I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2), 
> didn't find anything in BOL either
>
> Select ROUND(19.99, 0) works just fine
>
>
> Is this a bug or not? We develop accounting software and for us this is a 
> dangerous bug.

Odd. I just tried it in Server SQL 2000 SP3 on Windows 2003 SP1, and SQL 
Server 2005 SP2 on Windows 2003 SP2, and it returns 10.000000 on both which 
is expected. In both cases I used iSQL, so it may be down to connection 
properties.

-- 
Dan 

0
Dan
7/29/2010 2:10:54 PM
"Sven Peeters" <sven.peeters@systemat.com> wrote in message 
news:201072995556sven.peeters@systemat.com...
> Hi,
>
>
> I'm a bit dazzled why this statement won't work in T-SQL : Select 
> Round(9.990000, 0)
>
> The error I get is : Arithmetic overflow error converting expression to 
> data type numeric.
>
> The result should be : 10
>
> I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2), 
> didn't find anything in BOL either
>
> Select ROUND(19.99, 0) works just fine
>
>
> Is this a bug or not? We develop accounting software and for us this is a 
> dangerous bug.

The answer is very simple and not a bug.  What is the datatype of 9.990000? 
The following will tell you.

select 9.99000 as xx into testtb
exec sp_help 'testtb'

The moral:  If you use literals you must know what datatype the database 
engine uses to represent that value.  Alternatively, specify the datatype so 
there is no possibility of guessing. 


0
Scott
7/29/2010 2:28:11 PM
This is because 9.990000 is treated as NUMERIC(7, 6) and after the
rounding becomes NUMERIC(8, 6), so it overflows. To fix it you have to
cast explicitly the argument to correct precision and scale:

SELECT ROUND(CAST(9.990000 AS DECIMAL(8, 6)), 0);

Looking at the ROUND documentation in BOL this may be considered a
bug. According to this:

http://msdn.microsoft.com/en-us/library/ms175003.aspx

The return expression when the argument is NUMERIC(p, s) is
DECIMAL(38, s), so it should have resulted in no error.

--
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
7/29/2010 2:28:53 PM
Scott Morris (bogus@bogus.com) writes:
> The answer is very simple and not a bug.  What is the datatype of
> 9.990000? The following will tell you. 
> 
> select 9.99000 as xx into testtb
> exec sp_help 'testtb'

An alternate way to find the same information is this:

  declare @x sql_variant
  select @x = 9.990000
  select sql_variant_property(@x, 'Basetype'), 
         sql_variant_property(@x, 'Precision'), 
         sql_variant_property(@x, 'Scale') 


More to type, but creates no table.
 
> The moral:  If you use literals you must know what datatype the database
> engine uses to represent that value.  Alternatively, specify the
> datatype so there is no possibility of guessing. 
 
What is the datatype of a literal in SQL Server is definitely an trivial
thing, and there is more then one situation where results are not as 
expected. What about this:

   select power(10.0, -2), power(10.00, -2)

And, Dan... ISQL uses DB-library connects with ARITHABORT and ANSI_WARNINGS 
ON, so it lets you get away with it.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
7/29/2010 3:12:31 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DC4AF0ECFB63Yazorman@127.0.0.1...
> Scott Morris (bogus@bogus.com) writes:
>> The answer is very simple and not a bug.  What is the datatype of
>> 9.990000? The following will tell you.
>>
>> select 9.99000 as xx into testtb
>> exec sp_help 'testtb'
>
> An alternate way to find the same information is this:
>
>  declare @x sql_variant
>  select @x = 9.990000
>  select sql_variant_property(@x, 'Basetype'),
>         sql_variant_property(@x, 'Precision'),
>         sql_variant_property(@x, 'Scale')
>
>
> More to type, but creates no table.
>
>> The moral:  If you use literals you must know what datatype the database
>> engine uses to represent that value.  Alternatively, specify the
>> datatype so there is no possibility of guessing.
>
> What is the datatype of a literal in SQL Server is definitely an trivial
> thing, and there is more then one situation where results are not as
> expected. What about this:
>
>   select power(10.0, -2), power(10.00, -2)
>
> And, Dan... ISQL uses DB-library connects with ARITHABORT and 
> ANSI_WARNINGS
> ON, so it lets you get away with it.

I thought there might have been something in the connection properties which 
is why I mentioned it.

-- 
Dan 

0
Dan
7/29/2010 3:39:54 PM
Reply:

Similar Artilces:

newb: calling methods from DLL
I'm trying to call methods in a C++ dll from an MFC dialog app. The DLL *.h file is wrapped with Extern "C" and the prototypes look like this: extern BOOL __stdcall When making a call to one of the dll methods from my MFC app I get an unresolved external error. There is a .DEF file for the dll, but I'm not sure where I need to place it or reference it. I have added the dll's header file to my MFC app. What else do I need to do to use the dll and kill the linker errors? Thanks for any help, Steve My DLL does not appear to create a lib file.... that could be it, no?...

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

How to preserve conditional formatting on a web query table result
I have an external database that Excel queries and returns two columns of dates. I can set up a conditional format (in one colum) so that the dates in each row of the column change colour if the corresponding columns date is different. My problem is how to COPY and PASTE the conditional formatting across all dates in the one column (so that each cell looks at the date in the corresponding cell next to it)? Any ideas or suggestions? You can just use the format painter to copy and paste formats -- Regards, Peo Sjoblom "Simon L" <Simon L@discussions.microsoft.com>...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

Call Center Software
We have a fairly large call center (350+) taking tech support calls (100k/month) 7x24x365. Most of our customers have support contracts with us with a few that don't. We have created a customized solution that will take the contract and billing information and (through the use of eConnect) bring that information over to GP. It works for the most part but instead of having to continue investing more into custom development, we are looking for an out-of-the-box solution that would be integrated with GP 10. Key functionality would include call/case mgmt, contract mgmt and recurring...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

Show formula result in another sheet
sheet1 A:A contains a formula with some cells returning a value sheet2 A:A references sheet1 A:A with a simple formula: =sheet1!A1 above formula filled down in sheet2 A:A the problem is it is not showing the results from sheet1 anyone know why that should be? Sorry false alarm, I had calculation set to manual. Results are coming back as they should. Red faces all round. "Gotroots" wrote: > sheet1 A:A contains a formula with some cells returning a value > > sheet2 A:A references sheet1 A:A with a simple formula: > > =sheet1!A1 > ...

Round in an IF statement
Where does the Round go in the following formula: =IF($C$19<5000000,500000,IF($C$19>5000000,(D52/$D$95)*$C$19)) I want it to round to the nearest 1,000. =IF($C$19<5000000,500000,IF($C$19>5000000,round(D52/$D$95)*$C$19)) ******************* ~Anne Troy www.OfficeArticles.com "heater" <heater@discussions.microsoft.com> wrote in message news:92C2F692-9C0A-4CF6-A8B2-C496CFCF1F9C@microsoft.com... > Where does the Round go in the following formula: > > =IF($C$19<5000000,500000,IF($C$19>5000000,(D52/$D$95)*$C$19)) > > I want it to round to the nea...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

emailing autofilter results
A client is using 2007 autofilter to produce a filtered list, she then emails the results to a colleague still in filtered format. When the colleague receives the data it is the complete and unfiltered. I have tried this several times successfully on my own data, each time the data remains filtered. She says that she saves the data in it's filtered format. I've tried that also and it works fine. What can be going wrong? Any ideas would be gratefully receieved Diane If you e-mail a filter result it is better to copy only the visible data to a new workbook and sen...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

What's this web page feature called?
On the yahoo home page, if the mouse pointer hovers over items in the "My Favorites" list, a floating window opens up. http://m.www.yahoo.com/?r0=1258393277 Or www.yahoo.com which takes you to the first link. What's that feature called? I'm trying to find a way to disable it in IE 8. No clear responses since yesterday. I'm hoping maybe someone here has enough page design knowledge to just provide a bit of vocabulary. This group deals with Windows XP issues, the internetexplorer.general group would be a better place to ask. -- -- &qu...

Service Call
We frequently have the need to move an item which is on a Service Call to a different ticket. Most of the time, these were put on a PO. Current functionality does not allow the part to be moved to a different Call without cancelling the PO and then re-creating it - not a good/reasonable solution. At a minimum, there should be a utility/button to remove the PO reference from the parts line thereby allowing the part to be deleted/moved. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, cl...

Vendor Withholding Rate % how does it round?
Does anyone know the rounding rules the way Great Plains uses the withholding Rate % to the nearest penny etc? Thanks all ...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...

simple question
Okay, I have a very simple question. Where do the old posts go after they run off the last page of this newsgroup? Sometimes I would like to refer to a post that I remember seeing in October, but is no longer on the current section. The search function seems to only search current posts as well. If anyone has some input please email me at joep@siboneylg.com Thanks kindly. have you tried google groups, they keep archived old posts: http://groups.google.co.uk/groups?q=microsoft.public.greatplains&hl=en "Joe Proehl" <joep@siboneylg.com> wrote in message news:099701c5...

CRM Rollup 1: KB 911022. You call that a solution?
Hey folks, The CRM rollup 1 describes one of their patches that solves the problem of not being able to restrict the exporting or printing function. KB article 911022 (which is included in the rollup 1 of the CRM). Describes a method in which you have to create an alternate database, and have the registry key point to this db when doing printing or exporting. Well, I've done that, but it doesn't take a smart person to figure out that when a user runs a query in the crm, then goes to export that data, it's only as good as the last time the true CRM was backed up, and res...

Simple Macro is not so simple
Hi all, I am trying to record a macro which will automatically perform the insert > comment function to a selected cell. Should be easy right? Alas no Danny There's sample code on my web site, for inserting comments: http://www.contextures.com/xlcomments03.html Aardvark wrote: > Hi all, > > I am trying to record a macro which will automatically perform the insert > > comment function to a selected cell. Should be easy right? Alas no -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Wow! Nice one :-) Thank you M...

(Error 1004) Object Defined Error ...simple code
I have the following simple simple code: Dim s as String s = WeekdayName(1) ' Causes Error 1004 Why would the above call to "WeekdayName(1)" suddenly cause an Object Defined Error?? My program has been working nicely for awhile, but now all of a sudden it halts at the above line??? What the heck is going on??? Robert Crandal Hi Robert, It works fine for me. However, checking out Help, the first day of week parameter is supposed to default to Sunday (or 1) as the first day of the week but it appears to default to zero. Tested in xl2002 and x...

vb--simple simple
hi, anyone know how to copy a combobox selection to a specific cell b using vb code in a command box -- Message posted from http://www.ExcelForum.com i cant see this in the userform. i know you can do this with a normal combo box which is not in a for but lost when in a userform -- Message posted from http://www.ExcelForum.com What's a "command box"? Can you give an clear example of what you want to do? -- Jim Rech Excel MVP "stevieh >" <<stevieh.16v8yw@excelforum-nospam.com> wrote in message news:stevieh.16v8yw@excelforum-nospam.com... | hi, | |...

how can I change rectangle hole into round hole
How can I change rectangle hole into a round hole ? there should be a way. I don't want a rectangular hole but a circular hole in my drawing. Visio should make it much easier to do this! Hi PG, Not totally sure what you're doing. Normally when people ask about holes, I start talking about Visio's Boolean Operations. Try this: 1. Draw a big square 2. Draw a smaller circle inside the square 3. Select both shapes 4. Choose: Shape > Operations > Combine You should have a round hole in a square shape. Note that this will destroy any properties or data that you've ad...

=<expr> function call
Greetings, I am new to the MS Access product, but have experience writing DB middle ware in other environments. Right now my challenge is this: I want to write an expression in a report control that is a complex expression of the record fields. I see how to directly write an expression referring to the record fields like [Customer Name] and so on, and I also see how to make a function call passing one or more fields as function arguments as above, but what I really want to do is pass a reference to the current record, and then within my function be able to refer to all the different fiel...