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.

TO_CHAR Format Specifiers

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

Informix TO_CHAR Oracle TO_CHAR
1 %Y 4-digit year YYYY
2 %y 2-digit year, 20th century for 00-49 RR
3 %b Abbreviated month (Jan - Dec) MON
4 %B Month name (January - December) MONTH
5 %m Month number (0 - 12) MM
6 %a Abbreviated day (Sun - Sat) DY
7 %d Day (0 - 31) DD

Conversion Examples

Typical conversion examples:

Informix Oracle Sample Output
1 TO_CHAR(TODAY, '%Y-%m-%d) TO_CHAR(SYSDATE, 'YYYY-MM-DD') 2013-09-30
2 TO_CHAR(TODAY, '%d/%m/%y') TO_CHAR(SYSDATE, 'DD/MM/RR') 30/09/13
2 TO_CHAR(TODAY, '%d.%m.%Y') TO_CHAR(SYSDATE, 'DD.MM.YYYY') 30.09.2013

More Information

About SQLines

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.

You could leave a comment if you were logged in.