Search code examples
sql-serverdatetimems-accessimportdatetimeoffset

SQL Server Import Data: How to set datetime offset as default date type in imports?

We are importing large amounts of data from legacy systems. The data is coming from MS Access, where several tables have DateTime-typed data.

Unfortunately, users have entered wrong year values, like year '216'.

By default, MS SQL Server uses DateTime as date datatype. This causes import errors, as the min value for DateTime is January 1, 1753. If the date datatype would be DateTimeOffset, the import would be successful.

Is there any way to set make SQL server to use DateTimeOffset as the default type for date typed data when importing data?

Solution

  • Use the SQL Server Migration Assistant for Access (AccessToSQL)

    Then adjust the mapping of the fields: Edit Type Mapping (AccessToSQL)

    But why not correct these easily identifiable errors before the import?