The most likely reason for an Access date to fail in SQL Server is that the
date ranges supported by the two products differ. Access supports dates as
early as Jan 1, 100, while SQL Server only supports dates back to Jan 1,
1753 (and only Jan 1, 1900 if it is a smalldatetime data type).
Even if you don't think you have dates prior to Jan 1, 1753, remember that a
data entry error can easily cause a date that was supposed to be May 13,
1999 to be entered as May 13, 199.
Use Access to check for any dates prior to Jan 1, 1753.
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
> I have been trying to migrate my company's Access 2000 database to SQL
> server while maintaining the Access front-end.
> When trying to import the current tables, I get an error. The error
> is due to the date fields. They are date/time in Access 2000. I have
> tried importing them as either datetime or smalldatetime. No luck.
> They will import as text, but then I loose all sorting functionality
> on the date fields.
> I have scoured the Internet, all Microsoft Access public newsgroups,
> and checked on IRC. No one seems to have had this problem before.
> What am I doing wrong?
> Jonathan Roberts
> Advantage Innovation