Problems with Varchar in Sqlserver 2005

Question

Problems with Varchar in Sqlserver 2005

anand.narayanan 87953

Valued Member

Points: 60
More actions
June 20, 2012 at 10:14 am

#104158

Hi,
i have been working with Sqlserver for the past few years.
Today, i have came across a strange scenario.
in our application, we are facing Arithmetic overflow error converting varchar to numeric datatype.
actually we are using the varchar data type in our application (the Query is as follows) .
Select * from master_data where PlanNo = 12345678
here PlanNo is varchar. even though it is varchar, we didnt use single quotes (PlanNo = '12345678') to retrive data.
when we try to do transaction by fetching this data prior to 31 May 2012 (without single quotes as mentioned above), it is working. where as if we do the transaction on/after the date '01 Jun 2012' , it is throwing "Arithmetic overflow error converting varchar to numeric datatype" error.
is there any recent changes (like any patches released?)for SQLSERVER2005??
your assitance will be much apprieciated. 🙂

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

anand.narayanan 87953 (6/20/2012)
Hi,
i have been working with Sqlserver for the past few years.
Today, i have came across a strange scenario.
in our application, we are facing Arithmetic overflow error converting varchar to numeric datatype.
actually we are using the varchar data type in our application (the Query is as follows) .
Select * from master_data where PlanNo = 12345678
here PlanNo is varchar. even though it is varchar, we didnt use single quotes (PlanNo = '12345678') to retrive data.
when we try to do transaction by fetching this data prior to 31 May 2012 (without single quotes as mentioned above), it is working. where as if we do the transaction on/after the date '01 Jun 2012' , it is throwing "Arithmetic overflow error converting varchar to numeric datatype" error.
is there any recent changes (like any patches released?)for SQLSERVER2005??
your assitance will be much apprieciated. 🙂

Please post the results of the following:

select top 10 PlanNo from master_data where trandate >= '20120601' -- or what ever the transaction date column is called

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442467

June 20, 2012 at 10:19 am

#1503579

Also, can you post the DDL for the table?

anand.narayanan 87953 Valued Member Points: 60 More actions · Answer 3

Lynn Pettis,

Thanks for your reply...

again a strange thing!! that is i am able to retrive the results for top 7 records. where as

if i queried 'Select top 8 * from Master_Data where TranDate >'01 Jun 2012' then, it is throwing the same above mentioned error (in my last post).

how come???

Regards,

Anand N

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

anand.narayanan 87953 (6/21/2012)
Lynn Pettis,
Thanks for your reply...
again a strange thing!! that is i am able to retrive the results for top 7 records. where as
if i queried 'Select top 8 * from Master_Data where TranDate >'01 Jun 2012' then, it is throwing the same above mentioned error (in my last post).
how come???
Regards,
Anand N

Post the DDL of the table as Lynn has already asked for

Additionally you can try to select particular columns instead of selecting all using "SELECT *"

Check which is the column when added in the SELECT statement gives error

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

Have to make the comment, this is a classic example of using data types appropriately. If it's a number, store it as a number and refer to it as a number. Same thing if it's a date or a string or a whojamajamit. Inevitably you run into issues.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

anand.narayanan 87953 Valued Member Points: 60 More actions · Answer 6

Hi,

this is the exact statement which we are using.

SELECT pb.TransactID FROM Master_data pb INNER JOIN Transact ch on pb.PolicyNo = ch.PolicyNo and

pb.InstructionId = ch.ContHstid

WHERE pb.PolicyNo = @PolicyNo AND pb.TransactDate = @EffDate AND

pb.TypeId IN (2, 3) AND pb.StatusCode IN (0, 1) AND ch.status in (1,4,9)

we are using two tables.

1. Master_data

2. Transact

@PolicyNo and @EffDate are inputs which we are suppiling inputs.

PolicyNo varchar

EffDate Datetime

will post the DDL soon...

Lowell SSC Guru Points: 323546 More actions · Answer 7

ahh, since it is parameterized, what are the datatypes of @PolicyNo and @EffDate?

for parameters, you do not need the quotes if the parameter is the right datatype...maybe it's an int and you can simply fix the parameter definition.

PolicyNo varchar
EffDate Datetime
will post the DDL soon...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Please post the DDL (CREATE TABLE statements) for the tables Master_data and Transact. Do not include any of the extended properties if you are using those for documentation purposes.