Importing data form Access

[wolfert]

Hello,

I'm currently converting an access database to SQL-server.
I have imported all tables from Access 97 to SQL-server 2000 but somehow I get an error when I import 2 tables. SQL says:

===================
Error at destination for Row number 139. Errors encountered so far in this task: 1. Insert column ("Datum gereed",DB_TYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character for cast specification.
===================

The format of the access table for the specified filed is date/time.
The format of the field in the SQL-table is :smalldatetime. I have also tried datetime (without luck).
It only works when I change the field in the SQL-table to nvarchar. However when the import completes and I change the field back to datetime, I get another error and it won't work.

I have checked the record for invalued data but all data seems to be correct. I'm really stuck here. Other tables contain the same fields and this works. Why can't I import this table?
This is the mentioned row of one table that doesn't import: (the second row goes wrong).

OrderID Volgnummer Datum gereed Aantal Opmerking
107 2 5-7-2002 435600
107 3 14-8-2002 136400
108 1 30-5-2002 1134


Thank you,

Patrick

 

[rianto]

Hi..
I think that your data is trying to store the 'day to month' column and vice versa.
you may want to try using convert function to convert date format before you change the format to date time?

Regards,
Rianto

 

[wolfert]

Thanks but how do I use the convert function with the DTS import stuff. (I'm new to sql)?

Thanks,
Patrick

 

[SadOldGoth]

Hi Wolf,

I've recently come upon the same problem and it was down to the year in the date field being 1021. Make sure that your date is within Gregorian Calendar range.

Let me know how it goes,

Regards,

Jes