Oracle to SQL2008R2 (SqlDateTime overflow during migration data)

Question

Hi,

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  DateTime.MinValue = 1/1/0001 12:00:00 AM

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?

Answer 1

Hi Omg-cmd,

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