Conversion failed when converting the nvarchar value '26.19' to data type int

1

Hello.

I what I have:
Debit_Amount = SUM(Case When aud.DebitAmount Is Not Null Then aud.DebitAmount Else 0 End)

And I keep on getting the referenced error.

I have tried:
Cast, Convert, nvarchar, varchar ....

Any assistance, would be appreciated. Thank you.

2

Debit_Amount = SUM(Case When aud.DebitAmount Is Not Null Then aud.DebitAmount Else 0.00 End)

3

Thanks for the quick response.

After updating it, I am getting:
Arithmetic overflow error converting nvarchar to data type numeric

4

This seemed to do the trick paired with your solution.
Cast(aud.DebitAmount As Numeric(14,2))

Thanks for your assistance!

5

Well ... actually. I had a condition for a specific account, when I remove the condition to try and sum all data, I get.

Error converting data type nvarchar to numeric.

6

what is data type for Debit_Amount

7

Hello.

It's nvarchar

8

show us what you did that causes the above error. but the root of your issue is the fact that DebitAmount is varchar. Why is it varchar and not money data type or decimal.

Otherwise you will be doing all kind of circque du soleil conversions that will come to byte your behind, like it is doing now

1 Like
9

lol. Love the play on words. We receive it from a third party that way and they indicated that they would not change it.

10

I have been testing a few things on the field in question.

try_convert(numeric(9,2),aud.DebitAmount)

Worked for some of the data, but if there is a comma in the string, it returns a Null.

11

again, no idea what you are trying to do without any sample data and the code that is not working.

12

we receive it that way from third party that way? as a database file? a text file? or

13

You could get rid of the commas:

try_convert(numeric(9,2),replace(aud.DebitAmount,',',''))

14

That may not work as expected - it sounds like some of the data may be in a different language format, for example nnn.nnn,nnn

If that is the case - and you know the source culture you can use TRY_PARSE:

 Select try_parse('123.456,789' As numeric(10,3) Using 'El-GR')

If this is US English - and the comma is included as a thousands separator:

 Select try_parse('123,456.789' As numeric(10,3) Using 'en-US')
1 Like
15

I figured the data was

nnn,nnn.nn

For example, SELECT CONVERT(decimal(28, 8), '123,456.78') compared to SELECT CONVERT(decimal(28, 8), '123456.78')

16

It very well could be - but we haven't seen any sample data and expected results. I also have not tested TRY_PARSE to compare performance - but if the data is in a different language it might be a better option.

1 Like
17

the OP says "We receive it from a third party that way and they indicated that they would not change it." no idea what the data is.