[SQL] 날짜 및 시간 관련 문법 정리

* DATE_FORMAT

날짜 및 시간을 숫자가 아닌 사람이 알아보는 용도로 사용할 때
(ex. 영어 월, 영어 요일)

- 문법

DATE_FORMAT(데이터, "문법")

- Format Description

%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)

%I Hour (00 to 12)
%i Minutes (00 to 59)
%H Hour (00 to 23)
%h Hour (00 to 12)
%k Hour (0 to 23)
%l Hour (1 to 12)
%p AM or PM
%S Seconds (00 to 59)
%s Seconds (00 to 59)

%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%j Day of the year (001 to 366)
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %x
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %v

- Examples

SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");

• 결과
  June 15 2017

SELECT DATE_FORMAT("2017-06-15", "%m %d %y");

• 결과
  01 15 17

* DATEDIFF

날짜 및 시간의 차이를 계산할 때(단위 지정 가능)
-> 그냥 날짜 - 날짜 해서는 계산이 도저히 안되기 때문에 함수가 있는 것 같음

- 문법

DATEDIFF(단위, 시작날짜/시간, 종료날짜/시간)

라고 나오는데, 실제로 프로그래머스에서 돌려보면

DATEDIFF(시작날짜/시간, 종료날짜/시간)
단위를 빼야 문법에 맞는다......뭐가 맞는거야

- Format Description

년 YEAR YY, YYYY
월 MONTH MM, M
일 DAY DD, D
시 HOUR HH
분 MINUTE MI, N
초 SECOND SS, S
밀리초 MILLISECOND MS
주 WEEK WK, WW
분기 QUARTER QQ, Q

- Examples

SELECT DATEDIFF(DAY,   '2021-06-12', '2021-07-13') AS [일차이]
     , DATEDIFF(MONTH, '2021-06-12', '2021-07-13') AS [개월차이] 
     , DATEDIFF(YEAR,  '2021-06-12', '2021-07-13') AS [년차이]

• 결과
  31 1 0