Conversion Failed When Converting the varchar value

1

Hello.

Here is what I have:
SUM(Case When ah.TransactionCodeKey = 669 AND ah.TokenString Like '%18%Late%Charge%' Then REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,49,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'') Else 0 End)

This is what I get:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '25.00' to data type int.

I have tried Convert and Cast and get the same message.

Here is an example of the raw data. Thank you.

image
image1253×43 3.58 KB

2

SUM(Case When ah.TransactionCodeKey = 669 AND ah.TokenString Like '%18%Late%Charge%' Then REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,49,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'') Else 0.0 End)

3

Thanks for responding. When I use 0.0, I get:
Arithmetic overflow error converting varchar to data type numeric.

4

This seemed to do the trick. Thanks.

Cast(REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,49,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'') As Numeric (5,2)) Else 0 End

5

D'OH, yeah, I should have remember that. That minimal sizing for literals (i.e 0.0 becomes only decimal(2, 1)) is one of the more annoying things that SQL Server does.