Fix Date Errors When Importing Access Tables into SQL Server
Question details
Identify and correct date values or destination column definitions that cause an Access table import into SQL Server to fail.

- Product
- Microsoft Access and SQL Server Import and Export Wizard
- Device & OS
- not provided
- Scenario
- Importing an Access table, or an Excel worksheet, into a SQL Server table.
- Observed behavior
- The import wizard may reject rows containing invalid dates, out-of-range values, or values incompatible with the destination data type.
Identify the source date column and the destination table and column named in the import error. Check whether the source stores dates as Date/Time values or as text, and whether the destination must retain a time component.
Check date values and destination column mappings
Use this approach when the wizard rejects rows because a date value or destination data type is incompatible.
SQL Server date and datetime2 support dates from January 1, 0001, through December 31, 9999. The older datetime type starts at January 1, 1753, so a value accepted by Access can still be outside the destination range.
Impossible dates such as April 31 can appear in text fields, although a properly typed Access Date/Time field cannot contain an impossible date.
In Access, open the source table in Design View and check the affected field's data type. Open Datasheet View to inspect its values, including unusually early dates and text that does not represent a valid date.
In SQL Server Management Studio, open the destination table in Design and check the affected column's data type and nullability. Use date when only the date is needed, or datetime2 when both date and time are needed, if the destination design permits it.
In Access Datasheet View, correct or remove invalid and out-of-range values. If a value is missing, confirm that the destination column allows NULL before leaving it blank.
Run the SQL Server Import and Export Wizard again. On Select Source Tables and Views, select the table and choose Edit Mappings to review the destination column and data type before starting the import.

Validate text dates in a SQL Server staging table
Use a staging table when date values are stored as text and you need to find rows that SQL Server cannot convert.
Review Excel date columns with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for reviewing spreadsheet data. Open compatible Excel files in WPS Spreadsheets to inspect date columns before a SQL Server import; SQL Server mapping errors still need to be resolved in the import tools.

Frequently Asked Questions
Why does an Access date fail to import into a SQL Server datetime column?
SQL Server datetime starts at January 1, 1753, so an earlier Access date can be rejected. Check the destination column in SQL Server Management Studio and use date or datetime2 if the table design permits the wider range.
Should I use date or datetime2 for the destination column?
Use date if you only need the calendar date. Use datetime2 if you also need the time component. Review the destination column in SQL Server Management Studio and the mapping under Edit Mappings in the import wizard.
How can I find invalid dates stored as text?
Import the text into a SQL Server staging column, then query it with TRY_CONVERT using the intended destination type and a conversion style that matches the source format. Non-null values that return NULL need review.
Can a blank Access date be imported as NULL?
It can be imported as NULL if the source value is null and the destination column allows NULL. Check the destination column's nullability in SQL Server Management Studio and review the wizard mapping.




