Formatting Numbers in SQL Server

Question

How can I format the number 123.45678 to 123.45 in SQL Server 2008?

Answer 1

You can cast it to a numeric type that is only two decimals, like so:

select
    cast(123.45678 as numeric(10, 2))

Answer 2

You can declare the item as decimal(10,2) and then do a select on that item

e.g. f decimal(8, 2)

Answer 3

Casting to a decimal (10,2) as suggested by the current answers will work, if you're happy to round to 123.46.

However if you want a truncation (as you seem to imply in your provided example) then you might want to apply the ROUND function first:

SELECT CAST(ROUND(123.45678, 2, 1) AS DECIMAL(9,2))
---------------------------------------
123.45

You can also use the ROUND function to produce actual rounding, but it's functionally identical to doing the cast alone, though maybe slightly clearer in intention.

SELECT CAST(ROUND(123.45678, 2) AS DECIMAL(9,2))
---------------------------------------
123.46

Answer 4

How can I show qty of 28.55 as: 2855?