SSMA challenge with Oracle 0000-00-00 zero date fields

Question

I am using the SSMA migration tool to migrate a large Oracle db to SQL Server 2016. In many of my Oracle date fields I have zero date values 0000-00-00 and records with these date values are not migrating over. I get a Arithmetic operation resulted in an overflow error. I've tried a couple of different things - mapping the target date datatype to datetime2 and also changing the project settings - General - Migration - Custom mode to Replace unsupported dates with Null. But so neither of these approaches have worked. Would love to get some help. Thank you so much!
Becky KD

Answer 1

Hi @Becky Kent-Dobias ,

The values of date in SQL server is range from 0001-01-01 to 9999-12-31. The value 0000-00-00 is not supported. Please refer to MS document Date and Time data types.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@CathyJi-MSFT

Thank you Cathy - I was just hoping the SSMA tool had some way for me to handle these Oracle zero date values such changing them into Null values. Otherwise, my only other alternative is to fix the data in the source database which has hundreds of tables and many date fields in each table.

Answer 3

Hi @Becky Kent-Dobias ,

The "replace unsupported dates" setting should work if you use "Oracle Data Provider for .NET" to connect to Oracle. If you are using OLE DB/ODBC, then there might be issues.

Another approach could be to use custom select query for data migration. Navigate to Tools -> Project Settings -> General -> Migration and switch "Extended Data Migration Options" to "Show". This will display an additional "Data Migration Settings" tab when you select an Oracle table in the objects tree where you can provide custom SELECT query to use during data migration. The idea is to define a condition there to replace unsupported values with NULL or whatever value you wish. Refer to this article for a step-by-step guide: https://learn.microsoft.com/en-us/archive/blogs/ssma/how-to-perform-incremental-data-migration-using-ssma

Regards,
Alex.