DATE_FORMAT - Convert Date to String - MySQL to IBM DB2 Migration

In MySQL, DATE_FORMAT function converts a DATE or DATETIME value to string using the specified format. In DB2, you can use VARCHAR_FORMAT function.

Note that the DATE_FORMAT and VARCHAR_FORMAT use different format strings.

MySQL:

  -- Convert the current date to YYYYMM format
  SELECT DATE_FORMAT(NOW(), '%Y%m');
  # 201302

DB2:

  -- Convert the current date to YYYYMM format
  SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMM') FROM sysibm.sysdummy1;
  # 201302

DATE_FORMAT and TO_CHAR Format Specifiers

When you convert MySQL DATE_FORMAT function to DB2 VARCHAR_FORMAT function you have to map the format specifiers:

Typical conversion examples:

	MySQL	DB2	Sample Output
1	DATE_FORMAT(NOW(), '%Y-%m-%d)	VARCHAR_FORMAT(CURRENT_DATE, 'YYYY-MM-DD')	2013-02-14
2	DATE_FORMAT(NOW(), '%d/%m/%y')	VARCHAR_FORMAT(CURRENT_DATE, 'DD/MM/RR')	14/02/13

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, dmtolpeko@sqlines.com - February 2013.

You could leave a comment if you were logged in.