Conversion failed when converting the nvarchar value

Moxioron · November 29, 2019, 2:30pm

Hello. I am trying to include a comma with the result of a number and am getting the following error:

Conversion failed when converting the nvarchar value '82,574' to data type int.

Here is my code .... I have in in question in bold below. I tried Cast and Convert and am getting the same result. Where am I going wrong? Thank you for your assistance.

Select
CAST(ln.LoanType As varchar(10)) AS LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Purchase_Count_2019 = Format(Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2019' AND '11/28/2019' Then 1 Else 0 End),'###,###,###'),
Purchase_Total_2019 = Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2019' AND '11/28/2019' Then ah.TransactionAmount Else 0 End),
Purchase_Count_2018 = Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then 1 Else 0 End),
Purchase_Total_2018 = Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then ah.TransactionAmount Else 0 End),
Count_Diff = Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2019' AND '11/28/2019' Then 1 Else 0 End)-Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then 1 Else 0 End),
Total_Diff = Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2019' AND '11/28/2019' Then ah.TransactionAmount Else 0 End)-Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then ah.TransactionAmount Else 0 End),
Diff_Total_Perc = (Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2019' AND '11/28/2019' Then ah.TransactionAmount Else 0 End)-Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then ah.TransactionAmount Else 0 End))/Sum(Case When ah.TransactionType='DPA' AND ah.EntryDate Between '11/01/2018' AND '11/28/2018' Then ah.TransactionAmount Else 0 End)

From
Loan ln
Join LoanTypeDefinitions ltd
On ltd.LoanType=ln.LoanType
Join AccountHistory ah
On ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber

Where
ln.LoanType In ('5','8','34','55','69','89','97','99','103','104','105','108')

Group By
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription

Wim_Leys · November 29, 2019, 4:01pm

I don't think the error has anything to do with the bold FORMAT() function.

DECLARE @i INT = 12345623;

SELECT @i
	,format(@i, '###,###,###')

result:
12345623 12,345,623

Comment parts of the SELECT out and re-run the query.
Repeat until you found the source of the error.

The source could be in the WHERE clause too.

harishgg1 · November 30, 2019, 9:08am

hi

Conversion failed when converting the nvarchar value '82,574' to data type int.

i tried to test this ..

declare @variable nvarchar = '82,574'
select 'casting to int success',CAST(@variable as int)

as you can notice the value is 8
i think we need 82574 as the output

jeffw8713 · November 30, 2019, 5:25pm

@harishgg1 - I cannot reproduce your output, it fails on my system. I think the problem here is the language and the decimal separator. Most likely, your system uses the comma ',' as the decimal separator.

If the comma is supposed to be a decimal separator - then you cannot convert the numeric value to an integer directly, you need to cast/convert to a numeric data type then take the floor.

If the comma is supposed to be the thousands separator - then you can replace the comma with a blank and then cast/convert to int.

Declare @number nvarchar(20) = '82,574'

 Select @number
      , intValue = cast(replace(@number, ',', '') As int)
      , numericValue = cast(replace(@number, ',', '.') As numeric(5,3))
      , truncValue = floor(cast(replace(@number, ',', '.') As numeric(5,3)));

For the OP - remove the format function. SQL Server is trying to return that column as an integer and you are formatting it as a string - which cannot be converted to an integer because of the formatting.

You should let the presentation layer format the data in the appropriate format for their system.

harishgg1 · November 30, 2019, 5:48pm

hi jeff

I figured out why you could not reproduce my output
( i looked into what you said .. yes decimal seperator setting is there
but does not look like the issue here )

Reason is i had the variable declared as varchar
meaning it is varchar(1) ... so only the first character is taken

declare @variable varchar= '82,574'
select 'variable declared as only varchar',CAST(replace(@variable,',','') as int)

declare @variable varchar(100)= '82,574'
select 'variable declared as varchar(100)',CAST(replace(@variable,',','') as int)

harishgg1 · December 1, 2019, 9:55am

when i try this its failing just like for You

declare @variable varchar(100)= '82,574'
select 'variable declared as varchar(100)',CAST(@variable as int)