DATE_FORMAT - Convert Date to String - MySQL to Oracle Migration

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

Note that the DATE_FORMAT and TO_CHAR use different format strings.

MySQL:

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

Oracle:

  -- Convert the current date to YYYYMM format
  SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM dual;
  # 201302

DATE_FORMAT and TO_CHAR Format Specifiers

When you convert MySQL DATE_FORMAT function to Oracle TO_CHAR function, you have to map the format specifiers:

	MySQL DATE_FORMAT		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	%M	Month name (January - December)	MONTH
5	%m	Month (0 - 12)	MM
6	%a	Abbreviated day (Sun - Sat)	DY
7	%d	Day (0 - 31)	DD
8	%H	Hour (0 - 23)	HH24
9	%h	Hour (1 - 12)	HH or HH12
10	%i	Minutes (0 - 59)	MI
11	%s	Seconds (0 - 59)	SS
12	%T	Time (hours, minutes and seconds)	HH24:MI:SS

Typical conversion examples:

	MySQL	Oracle	Sample Output
1	DATE_FORMAT(NOW(), '%Y-%m-%d)	TO_CHAR(SYSDATE, 'YYYY-MM-DD')	2013-02-14
2	DATE_FORMAT(NOW(), '%d/%m/%y')	TO_CHAR(SYSDATE, 'DD/MM/RR')	14/02/13
3	DATE_FORMAT(NOW(), '%d-%M-%y')	TO_CHAR(SYSDATE, 'DD-MONTH-RR')	14-February-13
4	DATE_FORMAT(NOW(), '%d/%m/%y %T')	TO_CHAR(SYSDATE, 'DD/MM/RR HH24:MI:SS')	14/02/13 15:35:22

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.