[SQL] mysql Week 주 관련 함수 (date 조절)

sir.YOO_HWAN·2022년 4월 19일
0

SQL

목록 보기
23/31


1. WEEK(date[,mode])

  • 주 번호 반환 / 주의 시작 날짜가 일요일인지 월요일인지와 반환 값이 0 ~ 53또는 1~ 의 범위에 있어야 하는지 여부를 지정
  • mode 인수를 생략하면 default_week_format사용
Mode | First day of week |	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year
  • If the week containing January 1 has 4 or more days in the new year, it is week 1.
  • 4일 이상 포함되면 1주로 취급
mysql> SELECT WEEK('2008-02-20');
        -> 7
mysql> SELECT WEEK('2008-02-20',0);
        -> 7
mysql> SELECT WEEK('2008-02-20',1);
        -> 8
mysql> SELECT WEEK('2008-12-31',1);
        -> 53
        
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0
  1. YEARWEEK() function:
mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> '52'
  1. WEEKDAY(date)
  • Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).
mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1
WEEKOFYEAR(date)
  • Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).
mysql> SELECT WEEKOFYEAR('2008-02-20');
        -> 8

참고 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_weekofyear

profile
data analyst

0개의 댓글