question

BeckyKentDobias-9321 avatar image
0 Votes"
BeckyKentDobias-9321 asked AlexanderIvanov-MSFT edited

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

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-migrationsql-server-migration-assistant
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day,

Oracle 0000-00-00 zero date fields

This is not a date value but a string.

What is the exact data type of the column in the Oracle database?


Please go over the this post and understand the different between Date displaying format and Date storing format.



0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @BeckyKentDobias-9321,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BeckyKentDobias-9321 avatar image
0 Votes"
BeckyKentDobias-9321 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexanderIvanov-MSFT avatar image
0 Votes"
AlexanderIvanov-MSFT answered AlexanderIvanov-MSFT edited

Hi @BeckyKentDobias-9321 ,

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://docs.microsoft.com/en-us/archive/blogs/ssma/how-to-perform-incremental-data-migration-using-ssma

Regards,
Alex.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.