SQL Cast from int to decimal

Question

I have two columns in a table that are populated with integer values. For each record, I want to divide the values of those two columns and format the output so that the decimal point is moved to places to the right.

For example, if I have values of the two columns for one record as 4 and 1 and I want to divide 1 by 4 (so 1/4) then I want the output to be 25.00.

Here is the last thing I tried a bit ago:

CAST(Total AS Decimal(2))/CAST(TotalAnswers AS Decimal(2)) AS 'Percent' 

I have not been able to find a syntactical explanation of the CAST function to see what the parameter passed in for decimal represents. When I change it, it does sometimes change the number of places to the right of the decimal but the results are not always what I expect so I would like to get a little help on this.

Answer 1

Write it like this:

SELECT CAST(CAST(Total As float) / TotalAnswers * 100 As decimal(8, 2))

Answer 2

DECIMAL(2) is a decimal of 2 digits before and no digits after the decimal point. That doesn't allow you to have digits after the decimal point!

The DECIMAL(p,s) specification defines the number of total digits (p) and the number of digits after the decimal point (s) (where s cannot be larger than p and is 0 if ommitted).

You need to use something like DECIMAL(4,2) (4 digit total - 2 of which after the decimal point; so therefore: also 2 before the decimal point) or something like that - some digits before and some after the decimal point - then you'll see your desired result!

For details on the DECIMAL type and its syntax, consult the MSDN Books Online topic on decimal and numeric

Answer 3

There are actual a couple of parameters..

(p, s)

where

• p represents the "precision" - the total number of digits before and after the decimal point.
• s represents the "scale" - the number of digits after the decimal point.

By only giving 1 parameter, you are giving the precision only.

See MDSN.

Answer 4

from book-online for decimal data type: Numeric data types that have fixed precision and scale.

decimal[ (p[ , s] )] and numeric[ (p[ , s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision) The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.