MySQL STR_TO_DATE()

MySQL 날짜함수, 시간함수

 

STR_TO_DATE()

   - DATE_FORMAT() 의 반대

   - 문자열을 날짜형태로 바꾸어준다. (format에 주의)

 

STR_TO_DATE(str, format)

의 형태

 

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

 

The Server scans str attempting to match format to it. The format string can contain literal characters and foramt specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format. see the DATE_FORMAT() function description.

 

DATE_FORMAT 함수 

https://webobj.tistory.com/115

MySQL DATE_FORMAT()

mysql> SELECT STR_TO_DATE('01, 5, 2013', '%d, %m, %Y');                                                                             +------------------------------------------+
| STR_TO_DATE('01, 5, 2013', '%d, %m, %Y') |
+------------------------------------------+
| 2013-05-01                               |
+------------------------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT STR_TO_DATE('May 1, 2013', '%M %d, %Y');
+-----------------------------------------+
| STR_TO_DATE('May 1, 2013', '%M %d, %Y') |
+-----------------------------------------+
| 2013-05-01                              |
+-----------------------------------------+
1 row in set (0.00 sec)

 

 

Scanning starts at the beginning of str and fails if format is found not to match. Extra characters at the end of str are ignored.

 

mysql> SELECT STR_TO_DATE('a09:30:17', 'a%h:%i:%s');
+---------------------------------------+
| STR_TO_DATE('a09:30:17', 'a%h:%i:%s') |
+---------------------------------------+
| 09:30:17                              |
+---------------------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT STR_TO_DATE('a09:30:17', '%h:%i:%s');
+--------------------------------------+
| STR_TO_DATE('a09:30:17', '%h:%i:%s') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set, 2 warnings (0.01 sec)

 

mysql> SELECT STR_TO_DATE('09:30:17', '%h:%i:%s');
+-------------------------------------+
| STR_TO_DATE('09:30:17', '%h:%i:%s') |
+-------------------------------------+
| 09:30:17                            |
+-------------------------------------+
1 row in set (0.00 sec)

 

 

Unspecified date or time parts have a value of 0, so imcompletely specified values in str produce a result with some or all parts set to 0:

음.. 나는 그냥 NULL로 나오는데.

 

mysql> SELECT STR_TO_DATE('9', '%m');
+------------------------+
| STR_TO_DATE('9', '%m') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

 

 

 

음.. MySQL 버전에 따라서 아래 값들은 다르게 나오기도 한다.

 

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

 

 

 

참고 . 사용 가능한 지정자

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