Sql query Calculation error

Can someone please help me, this line in my query is throwing following error

Msg 8117, Level 16, State 1, Line 9
Operand data type varchar is invalid for multiply operator.
case when e.ytdintpaid = 0 then (e.currentintrate*e.currentprinbal*(30)/360) else convert(varchar, Cast(abs(e.ytdintpaid)/(3) as money)) end as AINTINC,

Open in new window

Who is Participating?
openshacConnect With a Mentor Commented:
That's because 940.13 isn't an int,

SELECT convert(int, '940.13')

will always fail.

SELECT cast(convert(decimal, '940.13') AS int)
and that works

So use convert it into it correct datatype, and then choose how you want to cast it.
One of you columns in not numeric, it's a varchar.

Work out which on it is and convert it using CONVERT()
another thing worth looking at is that your WHEN and ELSE return different datatype.

The column AINTINC has to have a consistent datatype across all parts of the CASE statement
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

fahVBAuthor Commented:
fahVBAuthor Commented:
i made following changes and getting a diff error
Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '940.13' to data type int.

fahVBAuthor Commented:
this is with change

case when convert(varchar(20), e.ytdintpaid) = '0' then convert(varchar(20),e.currentintrate)*(convert(varchar(20),e.currentprinbal)*(30)/360) else convert(varchar(20), Cast(abs(e.ytdintpaid)/(3) as money)) end as AINTINC,
fahVBAuthor Commented:
Thank you, this way it worked, appreciate it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.