Conversion of Oracle to_char (datetime) with format string to MySQL

The Oracle to_char (datetime, FMT) function converts datetime values to a string in the format specified by the FMT option.

MySQL has the date_format function that allows datetime values converting to a string in the specified format.

Sqlways converts the Oracle to_char function to the MySQL date_format function and converts elements of format string from Oracle to corresponding specifier in MySQL as specified in the following table.

Table 56. Conversion of Oracle to_char (datetime) with format string to MySQL

Mapping of datetime format specifiers between MySQL and Oracle
MySQL	Oracle (independently from register)	Description
%	Dy	Abbreviated weekday name (Sun .. SAT)
% B	Mon	Abbreviated month name (Jan .. dec)
% D	-	Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc .)
% D % E	Dd	Day of the month, numeric (00 .. 31) and (0 .. 31 ))
% J	Ddd	Day of year (001. 366)
% M % C	Mm	Month, numeric (00 .. 12) and (0 .. 12 ))
			% M	Month	Month name (January... December)
% F	-	Microseconds (000000 .. 999999)
% I	Mi	Minutes, numeric (00 .. 59)
% H % I % L	HH Hh12	Hour (01 .. 12) and (1 .. 12 ))
			% H % K	Hh24	Hour (00 .. 23) and (0 .. 23 ))
						% P	AM PM	AM or PM
% R	-	Time, 12-hour (HH: mm: SS followed by AM or pm)
% S % S	SS	Seconds (00 .. 59) and (0 .. 59 ))
			% T	-	Time, 24-hour (HH: mm: SS)
% U	WW IW	Week (00 .. 53), where Monday is the first day of week
% U	-	Week (00 .. 53), where Sunday is the first day of week
% V	-	Week (01 .. 53), where Sunday is the first day of week, used with % x
% V	WW IW	Week (01 .. 53), where Monday is the first day of week, used with % x
% W	Day	Weekday name (Sunday... Saturday)
% W	-	Day of the week (0 = Sunday .. 6 = Saturday)
% X	-	Year for the week, where Sunday is the first day of the week, numeric 4 digits; used with % v
% X	-	Year for the week, where Monday is the first day of the week, numeric 4 digits; used with % v
% Y	Yyyy Syyyy Iyyy	Year, numeric, 4 digits
% Y	YY Iyy	Year, numeric, 2 digits
-	J	Julian day; the number of days since January 1, 4712 BC.
-	Q	Quarter of year (1, 2, 3, 4; Jan-MAR = 1 ).
-	Rr	Given a year with 2 digits: · If the year is <50 and the last 2 digits of the current year are> = 50, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year. · If the year is> = 50 and the last 2 digits of the current year are <50, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
-	Rrrr	Round year. accepts either 4-digit or 2-digit input. if 2-digit, provides the same return as RR. if you don't want this functionality, then simply enter the 4-digit year.
-	W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
-	Sssss	Seconds past midnight (0-86399 ).
-	X	Local Radix character.
-	Y, yyy	Year with comma in the position.
-	Year Syear	Year, spelled out; "S" prefixes BC dates "-".
-	Yyy	3 digits of year.
-	Y	1 digit of year.
-	Iy	2 digits of ISO year.
-	I	1 digit of ISO year.
-	Ad A.D.	Ad indicator with or without periods.
-	BC B .C.	BC indicator with or without periods.
-	CC SCC	One greater than the first two digits of a four-digit year; "S" prefixes BC dates "-". For example, '20' from '123 '.
-	D	Day of week (1-7 ).
-	A.m. P. M.	Meridian indicator with periods.
-	Tzh	Time zone hour.
-	Tzm	Time zone minute.
-	Tzr	Time Zone region information.
-	Rm

Table 57. Example of conversion

Oracle	MySQL
create procedure sp_to_char_date_format as begin -- GET ACTUAL TIME AND DATE select to_char(sysdate,'DD-MON-YYYY:HH24:MI') from dual; end;	create procedure sp_to_char_date_format() begin -- GET ACTUAL TIME AND DATE select DATE_FORMAT(CURRENT_TIMESTAMP, '%e-%M- %Y:%H:%i') from dual; end;

From: http://www.blogjava.net/kylixlu/archive/2010/04/22/319121.html