MS-Access to SQL Server 2000 problem with datetime

Question

I'm trying to transfer some data from Access to SQL Server 2000 (there are no other way to do this sadly), I'm using Access to mssql to do the job. The program fails everytime it finds a datetime in this fashion:

03/07/2009 10.17.00

While it imports date like this:

29/12/2008

I don't understand where this problem come from so I ask you for your help.

Thank you very much.

Answer 1

this might come in handy. Never worked with that program (always used DTS for such tasks), so I don't know its specs to tell you more.

Answer 2

It looks like it doesn't liek the time having a . delimiter - can you run an update to change . to :, or change the select on the table to replace while selecting?

Answer 3

How do you know that the year has a valid century portion, that is 19 or 20, in all the records? There could be some dates in the table with a century that is 09 or whatever folks can manage to enter without noticing. Especially if the default data format of thier system only has a two digit year?

The default SQL Server small date/time field specification in SQL Server doesn't allow for dates outside 1900-01-01 through 2079-06-06