Oracle’s NumToYmInterval function converts the provided number value of specified time units or expression to an interval year to month literal. Usually, we use the interval values when dealing with dates.
MySQL provides a certain level of support for intervals but does not support storing values of this type in variables. Hence, MySQL does not have any functions that are able to return interval values. The functions working with date intervals are commonly used by reporting and other type of queries.
So, when you try to convert code with the NumToYmInterval function call, SCT will generate the following message “340 — Severity CRITICAL — MySQL doesn’t support the STANDARD.NUMTOYMINTERVAL(NUMBER,VARCHAR2) function. Create a user defined function.”
Thus, SCT provides a basic recommendation on how you may solve this critical issue. Below you will find the proven solution from our team.
Consider the following example:
CREATE PROCEDURE P_NUMTOYMINTERVAL_001
SELECT opendate + NumToYmInterval(1, 'YEAR')
WHERE id = 998;
However, MySQL supports the interval literal syntax. So, we will need to modify the source query code to use the Oracle’s Interval Literal syntax.
SELECT opendate + INTERVAL '1' YEAR
WHERE id = 998;
SCT will successfully CONVERT this code TO the following MySQL code:
[SQL]CREATE PROCEDURE SCT_DEMO.P_NUMTOYMINTERVAL_001()
DECLARE var_v_date DATETIME;
OPENDATE + INTERVAL '1' YEAR
WHERE ID = 998;
Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your database migration projects.
- Oracle to PostgreSQL migration with AWS SCT: how to handle unsupported NumToDSInterval function
- Oracle to MySQL migration with AWS SCT: how to convert variables of SYS_REFCURSOR datatype
- Oracle to PostgreSQL migration with AWS SCT: how to handle unsupported data types
- Oracle to MySQL migration with AWS SCT: How to convert BULK COLLECT clause
- Oracle to PostgreSQL migration with AWS SCT: How to convert INSERT ALL statement
Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.