Getting error message Arithmetic overflow error

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

General SQL Server Forums

New to SQL Server Programming

Getting error message Arithmetic overflow error

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-05-02 : 08:31:42

Hi,

I am getting the following error message

Arithmetic overflow error converting expression to data type datetime.

i have used the following line in my stored procedure:

DATEDIFF(day, EI.targetdate, DATEADD(minute, -TimeZone.Bias, getutcdate())) as DaysOverDue

And i am getting the error message due to this line.
Could someone please help me.

kalyan Ashis Dey

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 08:37:37

What are the data types for EI.targetDAte and TimeZone.Bias?

Jim

Everyday I learn something that somebody else already knew

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 08:40:20

datatype TimeZone.Bias is more then INT can handle. column TimeZone.Bias should cantain value with in range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

--------------------------
http://connectsql.blogspot.com/

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-05-02 : 08:45:46

TimeZone.bias is Int and EI.targetDAte is nvarchar

kalyan Ashis Dey

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 08:49:15

Try these
SELECT * from yourTable where isdate(tagetDate) = 0
SELECT MIN(bias),Max(bias) from TimeZone

Jim

Everyday I learn something that somebody else already knew

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-05-02 : 09:09:21

I got my issue...could u please teel me one thing

Here i use Datadiff. for example:
Datediff(day,@startdate,@enddate)
now my @startdate contains date in different format. Some of the dates are in dd-mm-yy format,some are in mm-dd-yy format and some are in yy-dd-mm format. So is it necessary to have all the dates in same formate..here all the dates are in @enddate are in same format and it is in mm-dd-yy format.
Please help me

kalyan Ashis Dey

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 09:20:02

You need to convert @StartDate and @EndDate to datetime data types. You may have to do something different for each format, especially the mm-dd-yy.

Jim

Everyday I learn something that somebody else already knew

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-05-02 : 09:27:15

ok...thanks for your help...but one more thing...is there any way to chage the date format for all the records for the @startdate fields.

Also please let me know is there any way to find out the fields which are in incorrect format.

kalyan Ashis Dey

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 10:38:57

SELECT isdate(yourColumn) = 0 from yourTable will give you all the records that SQL can't convert to a date for whatever reason. How to tell if '030510' is YYMMDD or DDMMYY is going to be impossible without some other field giving you a clue as to what was meant.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources