Arithmetic Overflow error - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Arithmetic Overflow error

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-24 : 23:44:43

Hi,

I have a stored procedure that takes in date as its input parameter

<code>
create storedprocedure mystoredproc
(
@fromDate NVARCHAR(50)
)
SET DATEFORMAT mdy;
SELECT * FROM TABLE WHERE CONVERT(DATETIME,DateColumnName,101) > CONVERT(DATETIME,@fromDate,101)
</code>

While calling the storedprocedure I say
exec mystoredproc '06-01-2006'

I am getting the error
Arithmetic overflow error converting expression to data type datetime.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 23:49:00

it is your data in DateColumnName. Is it all in MM-DD-YYYY ?

try using isdate() to list out the problem record

select * from table where isdate(DateColumnName) = 0

Lastly, why are you storing date in varchar / char ?

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-25 : 00:21:36

Thank you so much

the query select * from table where isdate(datacol) = 0

gave the erroneous rows deleting on which the stored proc started to work

quote:
Originally posted by khtan

it is your data in DateColumnName. Is it all in MM-DD-YYYY ?

try using isdate() to list out the problem record

select * from table where isdate(DateColumnName) = 0

Lastly, why are you storing date in varchar / char ?

KH

Benjamin78
Spammer

4 Posts

Posted - 2007-01-26 : 11:24:24

*** spam removed ***

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources