I have a problem with migrating data from Oracle to SQL2008 r2.
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
I've research the cause of error and looks like it happens when SSMA initialize variable with default
I found some temp solution for that (Project settings -> General -> Migration -> Replase unsuported dates = Replase with nearest supported date.)
Could someone help me with finding more correct way to hadle it?
- 已编辑 omg-cmd 2012年3月1日 7:51
Regarding to your description, you tired to migrate data from 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. If so, based on my research, the SQL Server datetime data type is limited in range to values from 1/1/1753 through 12/31/9999. SSMA will flag values that fall outside of the SQL Server datetime range in an assessment report. Note that SSMA can convert datetime to SQL Server datetime2, which was introduced in SQL Server 2008.
For more information, see Using Date and Time Data http://msdn.microsoft.com/en-us/library/ms180878(v=SQL.105).aspx in SQL Server Books Online.
Meanwhile the dates in Oracle range from 4712 B.C. to 4712 A.D. If a column of type DATE contains values that are out of range for SQL Server, you can try to select the alternative data type for the column, which is VARCHAR(19).
Additional the datetime data type does not accept dates earlier that January 1, 1753. You can try to overcome this limitation by using the new date data type introduced in SQL Server. For more information, please see the following link: http://www.sqlcoffee.com/Troubleshooting071.htm
Hope it is helpful, if you have any error please post for further troubleshooting.
Regards, Amber zhang