Oracle to SQL Server migration with SSMA: Converting interval literals

19 Oct 2021 Andrey Khudyakov, Roman Muzyka

Oracle enables you to use specific interval literals for indicating a certain period of time. Unfortunately, there is no direct analog for such literals in SQL Server. As a result, when you apply SQL Server Migration Assistant (SSMA) for converting Oracle code with interval literals, you receive an error O2SS0086.

The term ‘interval literals’ refers to a fixed data value, such as the constant value term. Interval literals can be specified in terms of seconds, minutes, hours, days, months, or even years. There are two types of interval literals in Oracle: DAY TO SECOND and YEAR TO MONTH. SSMA fails to convert both and generates the following error messages:

O2SS0086: The literal ‘INTERVAL DAY TO SECOND’ was not converted.
O2SS0086: The literal ‘INTERVAL YEAR TO MONTH’ was not converted.

Let’s find out how you can solve this problem and successfully convert Oracle interval literals to SQL Server.

Possible solutions

Let’s take a look at 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;

You will get the notification about the O2SS0086 error while converting this code.

You can fix this with the DATEADD SQL Server function that has the relevant datepart (for example, month-mm, year-yy, and so on) and the proper “+” or “-” sign.

Here is how the converted SQL Server code should look like:

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

Hence, this tried and trusted solution will help you convert Oracle interval literals to SQL Server.

Final thoughts

Do you have additional questions about database migration? Check our Jumpstart for the SSMA offer and contact ABCloudz. We will help you in your journey and facilitate your database migration project.

Also, please review our Blog for the blog post series on the most common SSMA issues pertaining to Oracle to SQL migration.