admin管理员组文章数量:1313251
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
Share
Improve this question
edited Jan 30 at 17:49
Dale K
27.5k15 gold badges58 silver badges83 bronze badges
asked Jan 30 at 16:56
LeahLeah
1475 bronze badges
7
|
Show 2 more comments
1 Answer
Reset to default 1I'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 |
本文标签:
版权声明:本文标题:sql server - Format to two decimal places if a non-integer or zero decimal places if an integer using format() only? - Stack Ove 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1741949463a2406621.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
money
is also a terrible data type to use (it's actually anint
under the hood with the decimal place moved). Use adecimal
with an appropriate precision and scale (such as adecimal(18,4)
). – Thom A Commented Jan 30 at 16:58int
; this means it doesn't behave as people expect because it's actually anint
. Phil Factor did an article on it a long time ago. – Thom A Commented Jan 30 at 17:59