[FIXED] How to return a number with two decimal places in SQL Server without it automatically rounding

Issue

This is an odd one, I have a cost price, and an average cost price. The avg is determined by the avg function and compared to the cost price it looks like the below:

Cost Price | Average Cost Price
   8.24            8.23897666

Now when i use the below code it changes it to 8.24 even though i am not specifying any round function

select cast(8.23897666 as numeric(18,2))

I've tried casting it as a float and still it rounds it to .24 even though i want it to only return 2 decimal places.

Can anyone shed any light on this please as i am unsure as to why this is happening? AS regardless of the number before the decimal place i was to return the full number and 2 decimal places.

Solution

You can use floor() and integer division:

select floor(8.23897666 * 100) / 100

Or better yet, use round() with a non-0 third argument:

select round(8.23897666, 2, 1)

Answered By - GMB

Answer Checked By - Timothy Miller (PHPFixing Admin)