Arithmetic overflow error converting int to data type numeric. - How to convert Int to Numeric

Question

0.00/5 (No votes)

See more:

Hi,

The following code throws "Int" to "numeric" conversion error.

Please help me out.

SQL

Begin
declare @tmptble TABLE(EmpId nvarchar(10),EmpMonth numeric(2,0),EmpYear numeric(4,0))
insert into @tmptble(EmpId,EmpMonth,EmpYear)
select emp_CDSID,MONTH(getdate()),YEAR(getdate()) from Employee
	declare @count numeric(2,0)
	declare @empid nvarchar(10)
	declare @month numeric(2,0)
	declare @year numeric(4,0)
	set @count = (select count(*) from @tmptble)
declare FirstCursor cursor for select EmpId,EmpMonth,EmpYear from @tmptble									
open FirstCursor																				
while (@count>0)
  begin
	fetch FirstCursor into @empid,@month,@year
	set @existsAttendance = (select count(*) from Attendance where EmpId=@empid and EmpMonth=@month and EmpYear=@year)
	-- [ ATTENDANCE INSERT ] --
	if (@existsAttendance = 0)
	   begin
           Insert into CDSI_DB.dbo.Attendance(EmpId,EmpMonth,EmpYear,SG,S1,S2,W,W1,W2,LOP,CO,SPL,OD,H) values(UPPER(@empid),@month,@year,0,0,0,0,0,0,0,0,0,0,0)
 	   end
          set @count=@count-1	
  end
       close FirstCursor
End

And, "Attendance table has EmpMonth and EmpYear as numeric datatype.

Posted 31-Jul-13 0:46am

NPSSR

Updated 31-Jul-13 3:32am

v3

Add a Solution

3 solutions

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)

Maciej Los · Answer 1 · 2013-07-31T01:24:00

Solution 3

Use this:

SQL

declare @tmptble TABLE(Col1 nvarchar(10),Col2 INT,Col3 INT) 

insert into @tmptble(Col1,Col2,Col3) 
select ID, MONTH(getdate()), YEAR(getdate())
from Employee

Month[^] and Year[^] funtion return Integer data type ;)

Posted 31-Jul-13 1:24am

Maciej Los

Comments

Adarsh chauhan 31-Jul-13 8:19am

@Maciej ...
you are right but I tried npsrajan's query and didn't find any error. It worked fine for me..

create table employee
(
id nvarchar(10),
name Varchar(10)
)
insert into employee values('emp101','Adarsh'),('emp102','Nikhil')

--select * from employee

declare @tmptble TABLE(Col1 nvarchar(10),Col2 numeric(2,0),Col3 numeric(4,0))
insert into @tmptble(Col1,Col2,Col3)
select ID,MONTH(getdate()),YEAR(getdate()) from Employee

select * from @tmptble

Output:
Col1 Col2 Col3
emp101 7 2013
emp102 7 2013

so i have a doubt that why its giving error at his side and not on my side.. any idea??

Maciej Los 31-Jul-13 8:37am

Yes, i know that npsrajan's query executes without error. I have no idea why it raises error in Him/Her case. I suspect that we see only part of query.
Cheers!
Maciej

NPSSR 31-Jul-13 9:25am

correct. Let me improve my question.

Maciej Los 31-Jul-13 11:47am

See solution 4 ;)

Adarsh chauhan 31-Jul-13 8:39am

Thanks for your response..:)

Maciej Los 31-Jul-13 8:43am

You're welcome. Call again ;)

Bernhard Hiller · Answer 2 · 2013-07-31T04:51:00

Solution 4

What about that line:

SQL

declare @count numeric(2,0)

Did it happen that the count was greater than 99?

Posted 31-Jul-13 4:51am

Bernhard Hiller

Updated 31-Jul-13 5:47am

v2

Comments

Maciej Los 31-Jul-13 11:47am

Hawk eye ;)
+5!

Bernhard Hiller 1-Aug-13 2:11am

Thanks.

NPSSR 1-Aug-13 2:53am

hi Bernhard.. I changed the @count data type to int.

declare @count int

but still getting the same.

Bernhard Hiller 1-Aug-13 3:02am

Check your queries step by step. Create the tmptable, then execute
insert into @tmptble(EmpId,EmpMonth,EmpYear)
select emp_CDSID,MONTH(getdate()),YEAR(getdate()) from Employee
(e.g. with SQL Server Management Studio)

NPSSR 1-Aug-13 6:56am

Hi,
I have checked.
Even, if i have changed the target column's (EmpMonth,EmpYear),@month,@year datatype
to Int, still showing the same error.

If i put numbers directly instead of @month & @year, It's working fine.

Juz say,
how to compare MONTH(GETDATE())'s return value with the 8, which is stored as numeric in a table column.

Sorry for taking your precious time.. :)

Dheeraj_Gupta · Answer 3 · 2013-07-31T01:10:00

Solution 1

Make sure that your temp table columns having same datatype as of your table's column.

Mark as answer if solved your query.

Regards,
Dheeraj Gupta

Posted 31-Jul-13 1:10am

Dheeraj_Gupta

Comments

NPSSR 31-Jul-13 7:16am

Hi Dheeraj,

I am taking only one column 'ID' from employee. And it's nvarchar(10) only.

NPSSR 31-Jul-13 7:18am

Month(GETDATE()) returns Int. How to convert int to numeric ?

Maciej Los 31-Jul-13 8:42am

You don't need to convert it. See comments to my answer. Your query executes without error.

Dheeraj_Gupta 31-Jul-13 7:18am

dear you are also performing the selection of month and year please perform proper casting of return values.

Dheeraj_Gupta 31-Jul-13 7:19am

use cast or convert method.