Error converting data type varchar to numeric

hi All,

This case statement is giving me the error "Error converting data type varchar to numeric". the column dbo.tblKMTermloanFees.base_min datatype is varchar.

 And please see below for sample data in the column. i have to convert this into number. Any suggestions guys how to do this, Thanks in advance. Shilpa.

 CASE
 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6
 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1))
 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Y%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 12
 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Q%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 3
 WHEN dbo.tblKMTermloanFees.base_min IS NULL THEN 0
 ELSE dbo.tblKMTermloanFees.base_min
END,

base_min
8/m       
150/y     
120/y     
8/m       
150/y     


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 3:55:14 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
784 Views

Similar Articles

[PageSpeed] 25

Where is this CASE statement being used?  In an ORDER BY clause?

Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
1/22/2009 4:21:03 AM

Hi,Shilpa

I have tested With below Example It is showing no error

GO

create table tblKMTermloanFees

(id int identity ,Base_Min varchar(20))

GO

insert into tblKMTermloanFees

Select '8/m'

union all

Select '150/y'

union all

Select '120/y'

union all

Select '8/m'

union all

Select '150/y'

GO

Select

CASE

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1))

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Y%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 12

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Q%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 3

WHEN dbo.tblKMTermloanFees.base_min IS NULL THEN 0

ELSE dbo.tblKMTermloanFees.base_min

end

from
tblKMTermloanFees

GO

--

--Output

---------------------------------------

8.000000

12.500000

10.000000

8.000000

12.500000

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
1/22/2009 4:39:48 AM

The case statement is used in Select clause. If i remove the case statement the query runs fine no error.


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 4:50:26 AM

 

Sharonrao123:

This case statement is giving me the error "Error converting data type varchar to numeric". the column dbo.tblKMTermloanFees.base_min datatype is varchar.

 And please see below for sample data in the column. i have to convert this into number. Any suggestions guys how to do this

Did you check to see that all the values in that column qualify for the query that you're trying to execute on them ?

I just tried to execute your query with the given data and the query ran well.  Below is a small display of the same.

 

declare @table table ( id int identity ( 1 , 1 ) , base_min varchar(50) )

insert @table ( base_min )
select '8/m'
union all select '150/y'
union all select '120/y'
union all select '8/m'
union all select '150/y'

select * from @table

select
id ,
CASE
WHEN base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6
WHEN base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1))
WHEN base_min LIKE '%/Y%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 12
WHEN base_min LIKE '%/Q%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 3
WHEN base_min IS NULL THEN 0
ELSE base_min
END
from
@table
  

Below is a case when you would get the same error.  I have just tried to reproduce the error to show you one of the possibilities of what could be wrong.

 

declare @table table ( id int identity ( 1 , 1 ) , base_min varchar(50) )

insert @table ( base_min )
select '8/m'
union all select '150/y'
union all select '120/y'
union all select '8/m'
union all select '150/y'
union all select '150a/y'

select * from @table

select id , 
 CASE
 WHEN base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6
 WHEN base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1))
 WHEN base_min LIKE '%/Y%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 12
 WHEN base_min LIKE '%/Q%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 3
 WHEN base_min IS NULL THEN 0
 ELSE base_min
END
from @table
  Notice that I've intentionally added one record containing some data which can not fit to your requirements.

Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
1/22/2009 4:51:10 AM

hi Raghav,

 I am not sure why you didnt get any error but if i remove the case statement the query runs fine.


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 4:52:14 AM

hi dhimant,

 There is a zero value, is that causing problem?

base_min
8/m       
150/y     
120/y     
0
0


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 4:56:46 AM

Hi,Sharonrao

I dont think 0 will be causing u problem

GO

create table tblKMTermloanFees

(id int identity ,Base_Min varchar(20))

GO

insert into tblKMTermloanFees

Select '8/m'

union all

Select '150/y'

union all

Select '120/y'

union all

Select '8/m'

union all

Select '150/y'

union all

Select '0'

GO

Select

CASE

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1))

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Y%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 12

WHEN dbo.tblKMTermloanFees.base_min LIKE '%/Q%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 3

WHEN dbo.tblKMTermloanFees.base_min IS NULL THEN 0

ELSE dbo.tblKMTermloanFees.base_min

end

from
tblKMTermloanFees

GO

--

--Output

---------------------------------------

8.000000

12.500000

10.000000

8.000000

12.500000

0.000000

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
1/22/2009 5:03:36 AM

Sharonrao123:
 There is a zero value, is that causing problem?
 

it should not cause you the error.  The error will be generated only if the left part of your value contains any characters which can't be converted to numeric.


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
1/22/2009 5:12:34 AM

Spot on dhimant, you were right i found this value 30-60/m. I avoided that value and the query work now,

Can you suggest how can i improve the case statement so that it will not give me an error  when there is an invalid value?


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 5:51:26 AM

i have used this works fine now, Thank you all for your time.

 

WHEN base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (IsNumeric(Base_Min), CHARINDEX ('/', Base_Min)-1))


Thanks in advance, Shilpa.
0
Sharonrao123
1/22/2009 6:02:21 AM
Reply:

Similar Artilces:

SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"
I'm still quite new to SQL, and so I may be missing something quite obvious. Apologies in advance if that's the case. I've included my query at the bottom of the message as it is quite long; my problem is that I get "Arithmetic overflow error converting numeric to data type numeric" when the first parameter to my COALESCE calls is not NULL. That is, the COALESCEs and the query work as I expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I get the error. I am also getting "Warning: Null value eliminated from aggregate.&...

PB 11.2/MS SQL 2005 Error converting data type varchar to numeric
Am getting the above error from PB 11.2 when executing a stored proc. The proc take no parameters. Once i get this error, i cannot execute the proc succesfully from PB 11.2 until i recompile the stored proc. At the same time the proc works fine from Query Analyzer, as well as from PB 7. What is PB 11.2 doing to mess this up? Any ideas....it's wrecking havoc on our production app. Forgot to add the error is C0195:SQLSTATE =3D 22018 On Dec 13, 4:00=A0pm, bre...@friesens.com wrote: > Am getting the above error from PB 11.2 when executing a stored proc. > The proc take no...

Error converting data type varchar to numeric.
following is the error I am getting. Exception Details: System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.Source Error: Line 107: " select '" & eventnumber & "','" & username & "','" & requestor & "', '" & bcode & "', max([Loan_ID]) from loan "Line 108: Dim myCommand1 As New SqlCommand(sql1, myConnection)Line 109: myCommand1.ExecuteNonQuery()Line 110:Line 111: Dim sql2 As String = " update Equipment set [Available_for_Loan] = 0 where barcode = '" &am...

Arithmetic overflow error converting varchar to data type numeric
Hello, i am getting "Arithmetic overflow error converting varchar to data type numeric"  while i am trying to cast the numeric value to varchar in SQL Server 2000(This is the Query  (cast(' + @Qty + ' as varchar(50)) If any knows plz help me, thanks.        "Arithmetic overflow error converting varchar to data type numeric" stands for error while converting varchar to numeric, and you say "trying to cast the numeric value to varchar", this is kind a confusing thing. Can you explain your problem more detailed?Nev...

error in SQL Store Proc ''Conversion failed when converting the varchar value to data type int'
Hi , I am having problem and error of 'Conversion failed when converting the varchar value to data type int ' CREATE PROC dbo.testingloop (DECLARE @testid varchar(max))ASBEGINSET NOCOUNT ONDECLARE @SQL varchar(600)SET @testvalue = CURSOR FORSELECT [test ID])  FROM Data42 where [test ID] = 36OPEN @testvalue      FETCH NEXT     FROM @testvalue INTO @testid      WHILE @@FETCH_STATUS = 0       BEGIN      ' My update statement here  ...

How to convert varchar data type into datetime data type without data lose
Hi all, i have stored date as varchar(30) data type and Date format is dd/MM/yyyy now i have to convert it into datatime data type can u help me in query ?  i have 330 records and each recod contain the Date in format of dd/MM/YYYY but due to varchar data type i can not perform calculation on Date so i have to convert into datetime without data lose. please help me in this regard. Best RegardsRameezWaheed  life is name of learning!Mark as an answer if it helps You can check these posts http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2039108&SiteID=17   ...

Help needed: arithmetic overflow error converting numeric to data type numeric
Hi, I have a SQL server 2005 db. The table structure (from big list of columns, only decimal columns I mentioned) below.  CREATE TABLE [Product].[MetricMeasures](    [MetricMeasureID] [int] IDENTITY(1,1) NOT NULL,    [ProductID] [int] NOT NULL,    [ProductSizeValue] [decimal](4, 2) NULL,    [ItemWeightValue] [decimal](3, 3) NULL,    [MasterCartonWeightValue] [decimal](3, 3) NULL,    [MasterCartonVolumeValue] [decimal](4, 4) NULL, CONSTRAINT [PK_MetricMeasures] PRIMARY KEY CLUSTERED ( &nbs...

System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.
Hi There, I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line: Length = Convert.ToDouble( txtLength.Text ) I'm also using the following lines to prepare my stored procedure call: arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5); arParms[9].Value = record.Length; My stored procedure has the following parameter definition: @Length decimal(9,3) My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error: System....

Error 8114 (rc -1) : Error converting data type varchar to n
Hi PBers, I'm using PB 6.5.1 build 1278 and MS SQL Server 7. I have 2 views: CREATE view V772EVEN as ( SELECT SQ_EVT, DE_TIT_EVT, dt_ini_evt, dt_fim_evt, cd_spr_rsp, convert(decimal(7),right(mt_rsp_evt,6)) as 'mt_rsp_evt' FROM drhu772..t772even WHERE sq_evt > 199800000 ) create view dbo.V772PEVT as ( SELECT sq_evt, convert(decimal(7),right(sq_aln,6)) as 'mt_epg' FROM drhu772..T772PEVT WHERE tp_aln = 0 and upper(left(...

Formview gives Error converting data type nvarchar to numeric error when comma is in the input
I have a formview that collects various data including money and decimal data. I had a problem where the $ was casuing the "Error converting data type nvarchar to numeric" error and think I corrected it by changing the format to {0:C} for the field in the data bindings. I am still having an issue with commas in the data in a decimal field. The field in the database is decimal (14,9) and the format in the textbox in the formview is {0:n}. The data being input is something like 123,456.0. This gives the Error converting data type nvarchar to numeric error. If you remove the comma, 1...

System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type int
 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@op...

Update a Tablefield with Concatenating 4 fields
Hi,Within the vb-script from an ASP.NET webpage i'm trying to update an sqltable. I want to update a particular tablefield. Using the String as below:The field docpad (important to now) is a VARCHAR field with length 250. After executing the code i become an error: Syntax error converting the varchar value '~/Uploadmap/' to a column of data type int.I do not understaend this error because the targetfield is type VARCHAR and not int.   This is the code i'm using. What is it what i'm doing wrong?  sql2.Append("UPDATE dbo.T_20001_Documenten SET docpad = &...

Error converting data type varchar to int.
can anyone see as to why I would get this error with the following SP?  ALTER PROCEDURE [dbo].[SP] @ID int = 0, @emailFrom VARCHAR(50) = Null, @emailDate VARCHAR(50) = Null, @emailSubj VARCHAR(50) = Null, @emailTxtBody VARCHAR(1000) = Null, @emailHtmlBody VARCHAR(1000) = Null AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Notes VARCHAR (8000) DECLARE @TicketID INT DECLARE @emailBody VARCHAR (1000) DECLARE @Length Int SET @Notes = '' SET @Length = LEN(@emailSubj) ...

Error converting data type varchar to bigint
Hello, I tried to search on forum for what i listed about but kept timing out. So thought i would just post it up here. SERVER: * Windows 2003 Server Standard SP2 (running on VM) * AMD Opteron 2.2Ghz with 2GB RAM * ZPM v6.3.2.700 * SQL Server 2005 (express) v9.001399.06 * ~1250 devices registered on server (All Windows PC/Servers) Getting this error when trying to query on the web console: Error converting data type varchar to bigint Selection: Devices Group = All Status = Enabled If i was to select Devices Group = Only Windows Status = Enabled It works. ...

Web resources about - Error converting data type varchar to numeric - asp.net.sql-datasource

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Rothesay building new arena, converting existing rink to fieldhouse
The Town of Rothesay plans to build a new arena and convert the existing one into a fieldhouse.

Resources last updated: 1/19/2016 6:29:43 AM