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

Becky Kent-Dobias 21 Reputation points
2020-09-22T22:36:08.823+00:00

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

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
494 questions
Accepted answer
  1. CathyJi-MSFT 21,091 Reputation points Microsoft Vendor
    2020-09-23T06:16:13.513+00:00

    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.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Becky Kent-Dobias 21 Reputation points
    2020-09-23T15:19:17.21+00:00

    @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.

    0 comments
  2. Alexander Ivanov 491 Reputation points
    2020-10-14T23:07:05.29+00:00

    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.

    0 comments