how to find cause of arithmetic overflow error

[bebblebrox]

Hi,

I'm inserting about 500,000 records during which i'm converting a string to a date. I get an error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

How can I figure out which records are causing the error?

 

[gmmastros]

There must be something wrong with 1 or more dates that you are inserting.

You could try to find the 'bad' data using the IsDate function.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

You can try selecting all rows where isdate(converted string to date) = 0.

That should list the errors so you can correct them.

Regards,
AA

 

[SQLBill]

What is the date format? SQL Server has to know how to interpret dates, for instance:

01/02/05
Is that January 2, 2005 or is it 1 February 2005, or is it 2001, February 5th?

SQL Server has the SET DATEFORMAT function that you can use at the beginning of your script to let SQL Server know how to interpret inputted dates. You follow it with the format you are using:
SET DATEFORMAT YMD
SET DATEFORMAT MDY
SET DATEFORMAT DMY

Check out the FAQs for this forum and you'll find some good information on working with Dates.

-SQLBill

Posting advice: FAQ481-4875