SQLTeam.com | Weblogs | Forums

Conversion failed when converting the nvarchar value

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

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.

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

@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.

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 )

:slight_smile: :slight_smile:

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)

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)