MySQL DATE_FORMAT()

MySQL 날짜함수, 시간함수

 

DATE_FORMAT()

   - 날짜를 지정된 형태의 문자열로 바꾸어준다.

 

DATE_FORMAT(date, format)

의 형태로 사용

 

Formats the date value according to the format string.

 

The specifiers shown in the following table may be used in the format string. The % character is required before format specifier characters. The specifiers apply to other functions as well: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP()

 

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	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, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	, for any “x” not listed above

Ramges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'

 

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable.

 

For the %U, %u, %V and %v specifiers, see the description of the WEEK() function for information about the mode values. The mode affects how week numbering occurs.

 

DATE_FORMAT() retuns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names contabing non-ASCII characters.

 

mysql> SELECT DATE_FORMAT('2020-04-1 :15:00:00', '%W %M %Y');

+-------------------------------------------------+
| DATE_FORMAT('2020-04-01 :15:00:00', '%W %M %Y') |
+-------------------------------------------------+
| Wednesday April 2020                            |
+-------------------------------------------------+
1 row in set (0.04 sec)

 

mysql> SELECT DATE_FORMAT('2020-04-01 :15:00:00', '%Y %m %d');
+-------------------------------------------------+
| DATE_FORMAT('2020-04-01 :15:00:00', '%Y %m %d') |
+-------------------------------------------------+
| 2020 04 01                                      |
+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_FORMAT('2020-04-01 :15:00:00', '%Y-%m-%d');
+-------------------------------------------------+
| DATE_FORMAT('2020-04-01 :15:00:00', '%Y-%m-%d') |
+-------------------------------------------------+
| 2020-04-01                                      |
+-------------------------------------------------+
1 row in set (0.00 sec)