Oracle, MySQL 날짜함수 비교

Oracle:

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

MySQL:

  -- Convert the current date and time to string (year-month-day)
  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d');
  # 2013-02-27

TO_CHAR and DATE_FORMAT Format Specifiers

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

Oracle TO_CHAR		MySQL DATE_FORMAT
YYYY	4-digit year	%Y
YY	2-digit year	%y
RRRR	2 or 4-digit year, 20th century for 00-49	%Y
RR	2-digit year, 20th century for 00-49	%y
MON	Abbreviated month (Jan - Dec)	%b
MONTH	Month name (January - December)	%M
MM	Month (1 - 12)	%m
DY	Abbreviated day (Sun - Sat)	%a
DD	Day (1 - 31)	%d
HH24	Hour (0 - 23)	%H
HH or HH12	Hour (1 - 12)	%h
MI	Minutes (0 - 59)	%i
SS	Seconds (0 - 59)	%s

Conversion Examples

Typical conversion examples:

	Oracle	MySQL
1	TO_CHAR(SYSDATE, 'YYYY-MM-DD')	DATE_FORMAT(SYSDATE(), '%Y-%m-%d)
2	TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')	DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s')
3	TO_CHAR(SYSDATE, 'DD-MON-YYYY')	DATE_FORMAT(SYSDATE(), '%d-%b-%Y')
4	TO_CHAR(SYSDATE, 'RRRR-MM-DD')	DATE_FORMAT(SYSDATE(), '%Y-%m-%d')

출처 : http://www.sqlines.com/