sql server - Format to two decimal places if a non-integer or zero decimal places if an integer using format() only? - Stack Ove

admin管理员组

文章数量:1313066

How do I format a SQL Server money data type to two decimal places if it is not an integer or zero decimal places if it is an integer using only the FORMAT function? i.e., can this be done without IIF, CASE, or REPLACE functions?

I thought this would work: FORMAT(@x, '0,0.##') However, this will format a value with only one significant digit past the decimal place to have only one decimal, whereas I want the format to be either zero decimal places or two decimal places. For example...

-- Format $123.00 to have zero decimal places
DECLARE @x AS money = 123
PRINT @x -- Original form
PRINT FORMAT(@x, '0,0.##') -- Correct format

-- Format $123.45 to have two decimal places. Note that this is technically unchanged, but I'm wanting to apply this method across a column of values that could have zero, one or two decimal places.
DECLARE @y AS money = 123.45
PRINT @y -- Original form
PRINT FORMAT(@y, '0,0.##') -- Correct format.

-- Format $123.40 to have two decimal places. Note that this is also unchanged, see example above for reasoning.
DECLARE @z AS money = 123.4
PRINT @z -- Original form
PRINT FORMAT(@z, '0,0.##') -- This is incorrect. It has one decimal place whereas I need two

How do I format a SQL Server money data type to two decimal places if it is not an integer or zero decimal places if it is an integer using only the FORMAT function? i.e., can this be done without IIF, CASE, or REPLACE functions?

I thought this would work: FORMAT(@x, '0,0.##') However, this will format a value with only one significant digit past the decimal place to have only one decimal, whereas I want the format to be either zero decimal places or two decimal places. For example...

-- Format $123.00 to have zero decimal places
DECLARE @x AS money = 123
PRINT @x -- Original form
PRINT FORMAT(@x, '0,0.##') -- Correct format

-- Format $123.45 to have two decimal places. Note that this is technically unchanged, but I'm wanting to apply this method across a column of values that could have zero, one or two decimal places.
DECLARE @y AS money = 123.45
PRINT @y -- Original form
PRINT FORMAT(@y, '0,0.##') -- Correct format.

-- Format $123.40 to have two decimal places. Note that this is also unchanged, see example above for reasoning.
DECLARE @z AS money = 123.4
PRINT @z -- Original form
PRINT FORMAT(@z, '0,0.##') -- This is incorrect. It has one decimal place whereas I need two

• sql-server
• t-sql
• format

Share Improve this question Follow edited Jan 30 at 17:49 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 30 at 16:56 LeahLeah 14755 bronze badges 7

• 2 This is a display issue; the matter has nothing to do with SQL Server. Though, also, money is also a terrible data type to use (it's actually an int under the hood with the decimal place moved). Use a decimal with an appropriate precision and scale (such as a decimal(18,4)). – Thom A Commented Jan 30 at 16:58
• 2 I'm inclined to say it is not possible. Best write a User Defined Function that does the stripping of unwanted zeroes so you can call it as e.g. FmtNum(@x) – Peter B Commented Jan 30 at 17:23
• 1 Because, as I said, it's actually a disguised int; this means it doesn't behave as people expect because it's actually an int. Phil Factor did an article on it a long time ago. – Thom A Commented Jan 30 at 17:59
• 1 What are you gonna do with that nicely formatted output? Usually printing things isn't sql servers end destination – siggemannen Commented Jan 30 at 17:59
• 1 Yeah, so as someone said, don't format it at all, the "different program" should do that job when it displays things – siggemannen Commented Jan 30 at 21:35

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 1

I'm not sure what you mean by ... can this be done without IIF, CASE, or REPLACE functions ..., but you may use an expression (not a constant) as pattern in the FORMAT function:

DECLARE @x AS money = 123
DECLARE @y AS money = 123.45
DECLARE @z AS money = 123.4

SELECT 
   x = FORMAT(@x, IIF(@x % 1 = 0, '0,0', '0,0.00')),
   y = FORMAT(@y, IIF(@y % 1 = 0, '0,0', '0,0.00')),
   z = FORMAT(@z, IIF(@z % 1 = 0, '0,0', '0,0.00'))

Result:

x	y	z
123	123.45	123.40

本文标签：