Answered by:
Basic doubt in Round function
Question

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  MSFTModerator Saturday, June 22, 2013 8:35 AM

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  MSFTModerator Saturday, June 22, 2013 8:35 AM

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 NModerator Thursday, June 13, 2013 2:18 PM
 Marked as answer by Allen Li  MSFTModerator Saturday, June 22, 2013 8:35 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 NModerator Friday, June 14, 2013 1:07 PM
 Marked as answer by Allen Li  MSFTModerator Saturday, June 22, 2013 8:35 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  MSFTModerator Saturday, June 22, 2013 8:35 AM



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  MSFTModerator Saturday, June 22, 2013 8:35 AM

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 NModerator Thursday, June 13, 2013 2:18 PM
 Marked as answer by Allen Li  MSFTModerator Saturday, June 22, 2013 8:35 AM

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 articles 

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 NModerator Friday, June 14, 2013 1:07 PM
 Marked as answer by Allen Li  MSFTModerator Saturday, June 22, 2013 8:35 AM