Error importing from SQL Server table with a datetime2(7) column

Question

When importing tables from a database that contain a database2(7) column I get the following error:

Data overflow converting to the data type for table 'People_dcfc0cd6-e06e-4476-a060-713e0897ab8f' column 'DateOfBirth'.
The current operation was cancelled because another operation in the transaction failed.

I am able to preview the tables and the dates are correctly displayed; it is just the import that fails.

Does anyone know how I can resolve this?

Answer 1

Same issue here. Did you find a solution yet?

Answer 2

I came across this same issue.  The problem is that the DATETIME2 datatype has a range from 0001/1/1 to eternity and DATETIME datatype has a range from 1753/1/1 to eternity.

Since the default value for DATETIME2 is 0001/1/1 and PowerPivot it is attempting to convert these values to DATETIME it throws an overflow error because 0001/1/1 is not a acceptable value for DATETIME

My solution was to select the 'Write a query that will specify the data to import' option in the Table Import Wizard and manually write a query to import the table.

To handle this field I would do the following:

SELECT CASE WHEN [DateOfBirth] = '0001/1/1' THEN Null ELSE [DateOfBirth] END As DateOfBirth
FROM People_dcfc0cd6-e06e-4476-a060-713e0897ab8f

You can change Null to any other default value you like and of course add any other fields to the query that you hope to import.