TO_CHAR - Convert Datetime to String - Informix to Oracle Migration

Informix and Oracle provide TO_CHAR function to convert a datetime value to a string using the specified format, but format strings are different.

Informix:

  -- Convert the current date and time to string (year-month-day)
  SELECT TO_CHAR(TODAY, '%Y-%m-%d') FROM systables WHERE tabid = 1;
  # 2013-09-30

Oracle:

  -- Convert the current date and time to string (year-month-day)
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
  # 2013-09-30

Note that we used '%Y-%m-%d' format string in Informix, and 'YYYY-MM-DD' in Oracle.

When you convert TO_CHAR function from Infrmix to Oracle you have to map the format specifiers:

Typical conversion examples:

• Informix to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - September 2013.