Here’s one more insight into common code conversion issues while using AWS SCT build number 660. This time, we will tell you how to convert NumToYmInterval from Oracle to MySQL.

Oracle has a NumToYmInterval function that converts the provided number value of specified expressions or time units to an interval year to month literal. Usually, interval values appear when it comes to dealing with dates.

The challenge

MySQL partially supports intervals. However, it doesn’t support storing values of this type in variables. This means that there are no MySQL functions that are able to return interval values. Meanwhile, MySQL functions working with date intervals are commonly used for reporting and other types of queries.

An attempt to convert code with the NumToYmInterval function call with SCT will bring you the following message: “340 — Severity CRITICAL — MySQL doesn’t support the STANDARD.NUMTOYMINTERVAL(NUMBER,VARCHAR2) function. Create a user-defined function.”

However, this challenge can be solved with a proper approach. Find the proven solution from our team below.

Potential solutions

Take a look at the following example:

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE P_NUMTOYMINTERVAL_001
AS
v_date DATE;
BEGIN
SELECT opendate + NumToYmInterval(1, 'YEAR')
INTO v_date
FROM account
WHERE id = 998;
END;

We know that MySQL supports the syntax of interval literals. This means that we will need to modify the source query code if we want to use Oracle’s Interval Literal syntax.

1
2
3
4
5
6
7
8
9
PROCEDURE P_NUMTOYMINTERVAL_001
AS
v_date DATE;
BEGIN
SELECT opendate + INTERVAL '1' YEAR
INTO v_date
FROM account
WHERE id = 998;
END;

SCT will successfully CONVERT this code TO the following MySQL code:

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE SCT_DEMO.P_NUMTOYMINTERVAL_001()
BEGIN
DECLARE var_v_date DATETIME;
SELECT
OPENDATE + INTERVAL '1' YEAR
INTO var_v_date
FROM ACCOUNT
WHERE ID = 998;
END;

Final thoughts

Did you find this material insightful? Make sure to check our blog for more posts helping you to solve common conversion issues with AWS SCT. Also, check our AWS Database Migration Support offer because our skilled team will help you take full advantage of AWS SCT.

Ready to start the conversation?