DTS date field from access to SQL server - Error datetime overflow

Question

DTS date field from access to SQL server - Error datetime overflow

zdenek douda

SSC-Addicted

Points: 419
More actions
August 18, 2004 at 9:25 am

#59403

Dear all,
I create DTS from access (columns date type) to SQL server (smalldatetime). All is OK, but by 2887th record occurs this E message:

"The number of failing row exceeds the maximum specified. Datetime field overflow."

Thanks for every ideas. zdenek

Viewing 4 posts - 1 through 3 (of 3 total)

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

Chad Carter Ten Centuries Points: 1089 More actions · Answer 1

I experienced the same thing before... If you switch the type to a datetime instead of a smalldatetime, it will probably work. That is what fixed my issue.

Chad

Bruce Gilpin Mr or Mrs. 500 Points: 558 More actions · Answer 2

You might also want to check the date values you are trying to insert. In DTS a date is valid as long as it consists of a valid year/month/day combination. In SQLServer however, it must be between 1/1/1753 and 12/31/9999. Any date outside of that range will result in the error you saw. Unfortunately, if that is the case the only thing you can do about it is change the date collumn to a character type and do it manually

Corie Curcillo SSC-Addicted Points: 418 More actions · Answer 3

make sure that if you are going to use smalldatetime that all of your dates are between "January 1, 1900, through June 6, 2079"

Corie Curcillo
MCT, MCDBA, MCSD