Decimal Place in T-SQL

Sometime decimal place can drive you into mad in T-SQL because different machine can show different result.

In SELECT statement, a formula would generate a result without decimal place, because it had been automatically rounded to integer:

Value_Is_200/3 would generate 67

The easiest way to control decimal place is to place decimal point in one of the constants in your formula:

Value_Is_200/3.0 would generate 66.7
Value_Is_200/3.00 would generate 66.67

If it does not work, you can try:

ROUND(Value_Is_200/3.0, 1) would generate 66.7
ROUND(Value_Is_200/3.0, 2) would generate 66.67

It may not work sometimes, because ROUND() is not for the purpose of decimal control. If it does not work, or if you need to truncate rather than round, try:

ROUND(Value_Is_200/3.0, 2, 1) would generate 66.6
ROUND(Value_Is_200/3.0, 3, 2) would generate 66.66

Sometimes, it still does not work. Then try:

CONVERT(DECIMAL(12,1), Value_Is_200/3.0) would generate 66.7
CONVERT(DECIMAL(12,2), Value_Is_200/3.0) would generate 66.67
or
CONVERT(DECIMAL(12,1), ROUND(Value_Is_200/3.0, 2, 1)) would generate 66.6
CONVERT(DECIMAL(12,2), ROUND(Value_Is_200/3.0, 3, 2)) would generate 66.66