SQL Compatibility in Migrating from MySQL to DM8

Replacing DATE_FORMAT() with to_char or to_date

If the DATE_FORMAT() function is used in MySQL, you can use the to_char or to_date function in DM for modification. For example:

• MySQL: select date_format(sysdate(), '%Year%Month') from dual
• DM: select translate(to_char(sysdate, 'yyyy-mm#'),'-#','Year_Month') from dual

convert Function Usage Guide

In MySQL, when using the convert() function, value is entered before type. In DM, value is entered after type. If the convert() function is used in DM, change the parameter sequence. For example:

• MySQL: CONVERT(CASE WHEN TEMP_STA.c_data_value THEN NULL ELSE TEMP_STA.c_data_value END, SIGNED) AS "ONLINEUSER"
• DM: CONVERT(INTEGER,CASE WHEN TEMP_STA.c_data_value THEN NULL ELSE TEMP_STA.c_data_value END) AS "ONLINEUSER"

cast Function Usage Guide

The usage of cast() in DM is the same as that in MySQL, but the effect is different. For example, in MySQL, there is no restriction on the value conversion from the numeric type to char type, but there is a restriction in DM. Convert cast(numeric type value as char) to cast(numeric type value as varchar). For the unsigned type, decide whether to perform conversion based on your requirements.

Replacing date_add with TIMESTAMPADD in DM

In MySQL, if the date_add function is used to evaluate the expression for adding a time interval, use the TIMESTAMPADD function instead in DM. For example:

• MySQL: select DATE_ADD(sysdate(), INTERVAL 1 YEAR);
• DM: select TIMESTAMPADD(SQL_TSI_YEAR, 1,sysdate());

interval Keyword Usage Guide

If an interval expression such as interval 1 year is used in MySQL, single quotation marks ('') must be added to the positive number following the interval keyword in DM. For example, interval '1' year. Negative numbers must be modified, for example, interval '-1' year.