Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.

The CAST() Function

The CAST() function converts an expression of one data type to another:

SELECT CAST(275 AS DECIMAL(5, 2));

Result:

275.00

In this example, we converted an integer (275) to a decimal value with a precision of 5 and with 2 decimal places.

Decimal and numeric are synonyms and can be used interchangeably. Therefore, we can adjust our example as follows to achieve the same result:

SELECT CAST(275 AS NUMERIC(5, 2));

Result:

275.00

It’s important to remember to adjust the precision as required:

SELECT CAST(18301275 AS DECIMAL(10, 2));

Result:

18301275.00

If the precision argument isn’t large enough, an error occurs:

SELECT CAST(18301275 AS DECIMAL(9, 2));

Result:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.

The CONVERT() Function

The CONVERT() function does the same thing as CAST(), except with a slightly different syntax:

SELECT CONVERT(DECIMAL(5, 2), 275);

Result:

275.00

Use an Arithmetic Operator

Another option is to use an arithmetic operator, such as a multiplication operator:

SELECT 275 * 1.00;

Result:

275.00

In this example, we multiply the integer by 1.00.

In SQL Server, whenever an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. The operation then returns the data type of the argument with the higher precedence.

In SQL Server, decimal has a higher precedence to integer.

By multiplying it by 1.00, we’re not changing the integer portion. We’re simply converting it to decimal and adding the fractional portion.

See Data Type Precedence in SQL Server for a list of data types in order of precedence.