2

I want to convert numeric value to Money but without Rounding value.W.r.t. to Link : https://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx It is rounding numeric to Money while casting.

But is it possible to give value upto 4 digit after decimal. NUMERIC VALUE : 123456789.3333 MONEY VALUE OUTPUT required : 123,456,789.3333

Learner
  • 31
  • 1
  • 6
  • 1
    `select cast(123456789.3333 as money)` doesn't do any rounding. – Martin Smith Dec 01 '17 at 16:06
  • What you are describing is how you want the string representation of your data. This should NOT be done in your query. Formatting is a presentation layer concept that should only be applied in the presentation layer. – Sean Lange Dec 01 '17 at 16:07
  • One mildly related comment is you really shouldn't use the money datatype in sql server. It is old and imprecise (and may actually be officially deprecated). This link does a good job of describing its issues: https://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server – snow_FFFFFF Dec 01 '17 at 16:12

3 Answers3

0

I guess you mean numerics where you have more than 4 digits, then you could use ROUND:

SELECT CAST(ROUND(123456789.33339, 4, 1) AS MONEY)
-- 123456789,3333

vs.

SELECT CAST(123456789.33339 AS MONEY)
-- 123456789,3334

Rextester Demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

May be you are looking for something like this

SELECT FORMAT(CONVERT(MONEY, CAST(123456789.3333  AS NUMERIC(18,4))), '###,###.####')

Result

123,456,789.3333

Nitesh Kumar
  • 1,774
  • 4
  • 19
  • 26
0

if you wanna split number as 3 digit , you can use this code in your select command

Select LEFT(CONVERT(VARCHAR, CAST(YourPrice AS MONEY), 1),  LEN(CONVERT(VARCHAR, CAST(UnitPrice AS MONEY), 1)) - 3 )as UnitPrice
hossein andarkhora
  • 740
  • 10
  • 23