Tags: code conversion, database conversion errors
In Oracle, you may use specific interval literals that indicate a certain period of time. SQL Server does not have a direct analog for interval literals. Thus, when trying to convert interval literals using SQL Server Migration Assistant (SSMA) for Oracle, you will get an error O2SS0086.
The term literal refers to a fixed data value just as the constant value term. You can specify interval literals in terms of years and months or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. SSMA can convert neither of them, so it generates one of the following error messages:
- O2SS0086: The literal ‘INTERVAL YEAR TO MONTH’ was not converted.
- O2SS0086: The literal ‘INTERVAL DAY TO SECOND’ was not converted.
Let’s see how you can convert Oracle’s interval literals to SQL Server.
Possible Remedies
Consider the following example.
1 2 3 4 | SELECT SYSDATE - INTERVAL '10-02' YEAR TO MONTH AS YTM, SYSTIMESTAMP - INTERVAL '2 5:30:20' DAY TO SECOND DTS FROM DUAL; |
Trying to convert this code, you will get the O2SS0086 error.
In order to fix this, you should use DATEADD SQL Server function with the relevant datepart (e.g. year – yy, month – mm etc.) and the appropriate sign “+” or “—”.
So, the updated SQL Server code looks as follows:
1 2 3 4 | SELECT DATEADD (MM, -2, DATEADD (YY, -10, GETDATE())) AS YTM, DATEADD(SS, -20, DATEADD(MI, -30, DATEADD (HH, -5, DATEADD (DD, -2, sysdatetime())))) AS DTS |
You can use this proven solution to convert interval literals from Oracle to SQL Server.
Related posts
- How to convert foreign keys with different types of columns and referenced columns
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert unsupported table expressions
- How to convert Unparsed SQL — Pivot Operator
- How to convert materialized view with float type
- How to convert interval expressions
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert unsupported SQL clause
- How to convert virtual columns
Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.
Source of technical information: Oksana Eremenko, Technical Lead at DB Best.
