Answered by:
Basic doubt in Round function
Question

hi all
i am having basic doubt in Round function.
select ROUND(744, 3)
giving 1000.
select ROUND(744.0,3)
Arithmetic overflow error converting expression to data type numeric.
how both are diff please explain me.
Thursday, June 13, 2013 1:14 PM
Answers

Try
select ROUND(cast(744.0 as decimal(12,1)) ,3)
Since you're using constant in your expression, SQL Server has to figure out the type of the variable. It uses the minimal precision, so that precision will not be enough to convert the value to a bigger number.
Here is a query (found in stackoverflow) that provides this information:
SELECT SQL_VARIANT_PROPERTY(744.0, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744.0, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744.0, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744.0, 'MaxLength') as MaxLength
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articles Proposed as answer by GertJan Strik Thursday, June 13, 2013 1:28 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:20 PM 
Check the differences by looking at the below:
The return type is decimal(4,1) for the ROUND(744.0,3). However, the value 1000.00 will not be able to hold in decimal(4.1).
Try the below: Declare @s decimal(4,1) = 1000.00
select ROUND(744, 3) select ROUND(744.0,3) SELECT SQL_VARIANT_PROPERTY(744.0, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744.0, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744.0, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744.0, 'MaxLength') as MaxLength SELECT SQL_VARIANT_PROPERTY(744, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744, 'MaxLength') as MaxLength
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Edited by SQLZealots Thursday, June 13, 2013 1:42 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:30 PM 
To expand on Latheesh's and Naomi's replies, all constants have datatypes. Integer constants are given datatype int, decimal values are given datatype numeric(p,q) where p is the number of digits (not counting leading zeros) in the number, and q is the number of digits to the right of the decimal point (including trailing zeroes). If you are not sure what datatype a given constant, Naomi and Latheesh have shown you code that will tell you want it is and if that is not the type you want it to be, then as Naomi has shown, you can use CAST() to change it to the desired datatype.
So 744 is an int, and 744.0 is a numeric(4,1).
ROUND() of an int returns an int, ROUND() of a decimal(4,1) returns a numeric(4,1).
So ROUND(744, 3) attempts to return 1000 as an int. Since int datatype can hold the value 1000, you get that value. But ROUND(744.0, 3) attempts to return 1000 as a numeric(4,1). But the largest possible value that can be in a numeric(4,1) is 999.9. So you get the overflow error.
Tom
 Proposed as answer by Naomi N Thursday, June 13, 2013 2:18 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:59 PM 
I'll try to explain the expression SELECT ROUND(744.0,3)
The first thing the parser does is determine what data type "744.0" is. It will interpret it as decimal, with the smallest scale and precision as possible to still accurately represent this exact value. In this case, that is decimal(4,1), which means up to 3 digits before the decimal point, and one digit after the digital point.
Next, you are rounding it "3", which means it should round up or down to the next 1000 value. In this case, it should round to 1000. However, the value 1000 does not fit in a decimal(4,1). It would require a decimal(5,1) or a decimal(4,0).
If you ever use ROUND with a negative "length", then it is best to first explicitly CAST the value to the datatype that accommodate the result.
In your case: SELECT ROUND(CAST(744.0 AS decimal(5,1)), 3)
Hope this helps.
GertJan
 Proposed as answer by Naomi N Friday, June 14, 2013 1:07 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Friday, June 14, 2013 8:07 AM
All replies

Try
select ROUND(cast(744.0 as decimal(12,1)) ,3)
Since you're using constant in your expression, SQL Server has to figure out the type of the variable. It uses the minimal precision, so that precision will not be enough to convert the value to a bigger number.
Here is a query (found in stackoverflow) that provides this information:
SELECT SQL_VARIANT_PROPERTY(744.0, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744.0, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744.0, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744.0, 'MaxLength') as MaxLength
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articles Proposed as answer by GertJan Strik Thursday, June 13, 2013 1:28 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:20 PM 
It is documented in MSDN clearly. Please look at the remark session of the below link:
http://msdn.microsoft.com/enus/library/ms175003.aspx
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thursday, June 13, 2013 1:22 PM 
my doubt is what is the difference between the 2 statement?
select ROUND(744, 3)
select ROUND(744.0,3)
this statement are same for round right. i am using(3) length, its round off only digits before decimal right.
Thursday, June 13, 2013 1:26 PM 
Check the differences by looking at the below:
The return type is decimal(4,1) for the ROUND(744.0,3). However, the value 1000.00 will not be able to hold in decimal(4.1).
Try the below: Declare @s decimal(4,1) = 1000.00
select ROUND(744, 3) select ROUND(744.0,3) SELECT SQL_VARIANT_PROPERTY(744.0, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744.0, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744.0, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744.0, 'MaxLength') as MaxLength SELECT SQL_VARIANT_PROPERTY(744, 'BaseType') as BaseType, SQL_VARIANT_PROPERTY(744, 'Precision') as Precision, SQL_VARIANT_PROPERTY(744, 'Scale') as Scale, SQL_VARIANT_PROPERTY(744, 'MaxLength') as MaxLength
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Edited by SQLZealots Thursday, June 13, 2013 1:42 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:30 PM 
To expand on Latheesh's and Naomi's replies, all constants have datatypes. Integer constants are given datatype int, decimal values are given datatype numeric(p,q) where p is the number of digits (not counting leading zeros) in the number, and q is the number of digits to the right of the decimal point (including trailing zeroes). If you are not sure what datatype a given constant, Naomi and Latheesh have shown you code that will tell you want it is and if that is not the type you want it to be, then as Naomi has shown, you can use CAST() to change it to the desired datatype.
So 744 is an int, and 744.0 is a numeric(4,1).
ROUND() of an int returns an int, ROUND() of a decimal(4,1) returns a numeric(4,1).
So ROUND(744, 3) attempts to return 1000 as an int. Since int datatype can hold the value 1000, you get that value. But ROUND(744.0, 3) attempts to return 1000 as a numeric(4,1). But the largest possible value that can be in a numeric(4,1) is 999.9. So you get the overflow error.
Tom
 Proposed as answer by Naomi N Thursday, June 13, 2013 2:18 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Thursday, June 13, 2013 1:59 PM 
BTW, this is an interesting question and a good TechNet article can be created based on it. Tom or Latheesh, do you want to create an article based on it and put it for June Contributions competition? (See sticky thread at the top of the forum). If not, I can create that article next week or Sunday.
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articlesThursday, June 13, 2013 3:19 PM 
Hi Naomi
thanks for ur help, but still i didnt get clear idea.can u please share your article once you done with that.
Thanks in advance.
Friday, June 14, 2013 7:00 AM 
I'll try to explain the expression SELECT ROUND(744.0,3)
The first thing the parser does is determine what data type "744.0" is. It will interpret it as decimal, with the smallest scale and precision as possible to still accurately represent this exact value. In this case, that is decimal(4,1), which means up to 3 digits before the decimal point, and one digit after the digital point.
Next, you are rounding it "3", which means it should round up or down to the next 1000 value. In this case, it should round to 1000. However, the value 1000 does not fit in a decimal(4,1). It would require a decimal(5,1) or a decimal(4,0).
If you ever use ROUND with a negative "length", then it is best to first explicitly CAST the value to the datatype that accommodate the result.
In your case: SELECT ROUND(CAST(744.0 AS decimal(5,1)), 3)
Hope this helps.
GertJan
 Proposed as answer by Naomi N Friday, June 14, 2013 1:07 PM
 Marked as answer by Allen Li  MSFT Saturday, June 22, 2013 8:35 AM
Friday, June 14, 2013 8:07 AM