[Mysql] Select할 때 String 을 Date로 표현하기, 포맷 변환

ex)

데이터 : varchar (8) 

20170316

변환하고 싶은 형태

2017-03-16

DATE_FORMAT(STR_TO_DATE(date, '%Y%m%d'),'%Y-%m-%d ')

*DATE_FORMAT ( Date, format )

Date타입의  값을 format에 지정한대로 출력해준다.

* STR_TO_DATE( string, format )

The STR_TO_DATE() converts the str string into a date value based on the fmt format string. The STR_TO_DATE() function may return a DATE , TIME, or DATETIME value based on the input and format strings. If the input string is illegal, the STR_TO_DATE() function returns NULL

String 형태의 타입을 포맷 형태에 맞춰 Date 형태로 바꾸어준다. Date, DateTime 형태를 반환해준다. 

만약 잘못된 형태의 String이 들어오면 Null값을 반환해준다.

* 데이터 포맷

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
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; 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	x, for any 'x' not listed above

출처 : http://kwonnam.pe.kr/wiki/database/mysql/date

특이하게 %i가 분이다. 원래그런가

HH:mm:ss만 하다가 이렇게 보니 어렵군

ex) 

SELECT DATE_FORMAT(STR_TO_DATE(stat_date, '%Y%m%d'),'%Y-%m-%d') AS DATE,

DATE_FORMAT(IFNULL(STR_TO_DATE(stat_time, '%H%i%s'), STR_TO_DATE(CONCAT(stat_time,"00"), '%H%i%s')) ,'%H:%i:%s') AS TIME

, stat_date

        , stat_time

결과 )