sql server error converting data type varchar to numeric.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

hello friends...

i have following query.

SQL

declare @month int
declare @year int
set @month=8
set @year =2014

declare @startdate datetime
declare @enddate datetime
set @startdate=CONVERT(datetime,convert(varchar,@year)+'-'+convert(varchar,@month)+'-'+'01')

if (@month>= MONTH(GETDATE()) and @year>=YEAR(GETDATE()))
begin
	set @enddate=(select GETDATE()) 
end
else
begin
	set @enddate=DATEADD(day,-1,DATEADD(month,1,@startdate))
end

create table #suspended_alumina_gpl
(
	edate datetime,
	avgliq decimal(18,5)
)

while (@startdate<=@enddate)
begin
	
	insert into #suspended_alumina_gpl 
	select convert(datetime,@startdate),isnull(AVG(spent_liquor),'') from suspended_alumina_gpl
	where CONVERT(varchar,edatetime,103)=CONVERT(varchar,@startdate)
		
	set @startdate=DATEADD(day,1,@startdate)
end
select * from #suspended_alumina_gpl

but it throws an error as follows while executing it.

Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to numeric.

Posted 18-Aug-14 23:33pm

TAUSEEF KALDANE

Updated 18-Aug-14 23:36pm

Magic Wonder

v2

Add a Solution

3 solutions

Solution 3

Mr. Karthik and Vinay suggested well
but if u want to put '' when your AVG(spent_liquor) is null then just change the datatype of your
"avgliq" field in the temp table to "FLOAT"
problem solved

Posted 19-Aug-14 0:06am

bkthebest

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2014-08-18T23:46:00

Hi
May be Issue is here

isnull(AVG(spent_liquor),'')

if the AVG value is NULL then you are trying insert Empty into decimal field (avgliq), So set it to "0" or some other numeric value. Do not set Empty character.

So use like below:-

SQL

insert into #suspended_alumina_gpl
    select convert(datetime,@startdate),isnull(AVG(spent_liquor),0) from suspended_alumina_gpl
    where CONVERT(varchar,edatetime,103)=CONVERT(varchar,@startdate)

hope it helps.

Vinay Mistry · Accepted Answer · 2014-08-18T23:46:00

Solution 2

if you are inserting avg value in numeric field then use

SQL

isnull(AVG(spent_liquor),0)

Posted 18-Aug-14 23:46pm

Vinay Mistry

Comments

TAUSEEF KALDANE 19-Aug-14 6:04am

thank you vinay sir

sasyu 27-Oct-14 15:08pm

Hi,

MY scenario is In staging the the data type of field is varchar for example
select Equipment_Size from dwpsa..STG_CAP_QUOTE_DTL
result :2.0yd(S)

IN core the column is loading by changing to numeric
as 2.00000

so my question is how can change the varchar to int (2.0yd(S) to 2.00000)
i tried many examples but no use can any one help me please....