You may have dates in the Access database that are out of range for SQL Server. Access supports dates as far back as Jan 1, 100, while SQL Server only supports dates back to Jan 1, 1753. Check the Access data for bad entries (missing chunks of the date).
I think the problem is that field contained times in the format 06:00:00 . Does SQL Server have a problem with this or is there a specific data type just for times?
I had the same problem with valid dates (2099-12-31). The Import Export Wizard in SQL Server automatically creates a table when the table does not exist when importing.
In Access (my source) the datetime field is created in SQL Server (my destination) as a SmallDatetime field.
SmallDatetime: Date and time data from January 1, 1900, through June 6, 2079
Datetime: Date and time data from January 1, 1753 through December 31, 9999
So I changed the definition in SQL Server from SmallDatetime to DateTime and the import succeeds.
> So I changed the definition in SQL Server from SmallDatetime to DateTime and the import succeeds.
Is that I changed the datatype of the 'EndDate' Column in the table design of the table where the import fails (by means of the SQL Enterprise Mgr). You can also use the ALTER TABLE statement in transact sql to do so.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.