0

I have a varchar value that needs to be casted into a money value. Tee value I like to cast looks like this:

enter image description here

I thought this should work

select 
cast(replace([Gross value transactions], '$','')as numeric) as amount
from xy

I get the following error:

Error converting data type nvarchar to numeric.

Is this because of the commas and dots? Like in $2,535,000.00? How do I get rid of this? Can I directly cast it as money?

@Thom A I tried actually to cast it into money directly:

 ,cast([Gross value transactions] as money) as amount

I got the following Error Message: Cannot convert a char value to money.

Not much more luck with this:

,cast(replace(replace(replace([Gross value transactions (NZD)], '$',''), ',',''), '.','') as numeric) as amount

Error converting data type nvarchar to numeric.

How can I cast it sucessfully?

Anna
  • 444
  • 1
  • 5
  • 23
  • `CAST` it to a `money` first then a `numeric`/`decimal`, as the `money` data type allows currency characters and thousand separators. – Thom A Mar 28 '23 at 09:11
  • 2
    Don't use money, https://blog.greglow.com/2018/01/15/sql-newbie-mistake-1-using-float-instead-decimal/ – jarlh Mar 28 '23 at 09:32
  • why does the repace function not work? – Anna Mar 28 '23 at 09:35
  • @ThomA - how do I do that ? – Anna Mar 28 '23 at 09:47
  • @jarlh to *store* the data, correct, but the OP has already made the incorrect choice to store the monetary value as a `varchar`. The *real* solution is the fix the design and store it in a `decimal` with an appropriate precision and scale (perhaps a `decimal(18,4)`). Casting the `money` as a middleman isn't the same as store is as `money`. – Thom A Mar 28 '23 at 09:49
  • *"Cannot convert a char value to money."* Sounds like you have some bad data in your column; another reason why using a `varchar` is a *terrible* idea for monetary values. You can use `TRY_CAST`/`TRY_CONVERT` but any bad data will end up being `NULL`; you really need to fix your design and fix the bad data. – Thom A Mar 28 '23 at 09:53
  • @jarlh That is a rather poor article that first discusses that using *`float`* is wrong, and then makes an implicit unfounded assumption, at a glance, that using `money` is almost equally wrong and that the author dislikes it too because it has relationships with currency symbols, and proceeds to show that using `decimal` is correct. Why would `money`, being a fixed-point data type, be inferior to `decimal(18,4)` which is also a fixed-point data type? – GSerg Mar 28 '23 at 10:31
  • @GSerg `money` has rounding issues, which are not outlined in that article, but are elsewhere https://stackoverflow.com/questions/1355418/what-data-type-should-i-use-to-store-monetary-values – Charlieface Mar 28 '23 at 11:55
  • @Charlieface Arguably, it's the decimal that has [non straightforward division](https://stackoverflow.com/a/424052/11683). When you divide two `decimal(19,4)`s, the result is typed as `decimal(38,19)` according to the [complicated rules](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms190476(v=sql.90)). That result is then rounded to `decimal(19,4)`. `Money`, on the other hand, does not change its precision throughout, so the digits that fall behind the four decimal places are ignored. Just like it is with the integer division. – GSerg Mar 28 '23 at 13:11

2 Answers2

0

Test sample

[Gross value transactions]: $2,535,000.11

Cast to money

SELECT cast([Gross value transactions] as money) as amount
FROM [dbo].[XY]
-- amount: 2535000.11

Cast to numeric/decimal

-- do not replace '.'
SELECT cast(replace(replace([Gross value transactions], '$',''), ',','')as numeric(18,2)) as amount
FROM [dbo].[XY]
-- amount: 2535000.11
Antony Kao
  • 114
  • 4
0
select parse([Gross value transactions] as money using 'en-us')
from xy;
GSerg
  • 76,472
  • 17
  • 159
  • 346