Blog: Oracle to SQL Server migration with SSMA: How to convert interval literals

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.

convert interval literals with SSMA for Oracle

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

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.