Issue Using Cast Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue Using Cast Function

Author

Topic

wabaker95
Starting Member

12 Posts

Posted - 2011-01-27 : 14:30:21

I am trying to call a function that I created in sql.

Misc009, Misc010, and Misc002 are all varchar columns in the Sales_Campaign_Participants table

The variable @Misc018 is declared as a float.

Select @Misc018 = dbo.LoanPmt(Convert(Float, Misc009) , Convert(Integer, Misc010), Convert(Float, Misc002)) From Sales_Campaign_Participants

Here is the LoanPmt function
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[LoanPMT] (@MonthlyRate float, @NumberOfMonths int, @Amount float)
returns float
as
begin
return (@amount*@monthlyrate/(1-power(1+@monthlyrate,-1*@numberofmonths)))
end

Why am I receiving the error message Error converting data type varchar to float?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-01-27 : 15:23:26

Does Misc09,Misc010,Misc002 Contains Non Numeric Value..You can use Isnumeric Function to select. Try Converting Numeric to Decimal.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 01:38:23

I Think you are trying to pass non-numeric values to the function where this is called!

wabaker95
Starting Member

12 Posts

Posted - 2011-01-28 : 16:38:32

Thank you guys. What I found out was the misc002 column contained values like $21,500 and was returning the function IsNumeric as true however I ended up having to programmatically remove the comma and dollar sign.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources