[mysql] 현재기준 월요일~일요일, 특정 요일 조회

mysql 현재 기준 각 요일 별 일자 조회

-- 2021.06.15 (화) 기준
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+0); -- 0  : 월요일 2021-06-14
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+1); -- 1  : 화요일 2021-06-15
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+2); -- 2  : 수요일 2021-06-16
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+3); -- 3  : 목요일 2021-06-17
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+4); -- 4  : 금요일 2021-06-18
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+5); -- 5  : 토요일 2021-06-19
SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+6); -- 6  : 일요일 2021-06-20

 

월요일 일 때,

where date_format(date_column, '%Y-%m-%d') = (SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())+0))

월요일 ~ 일요일 까지 

where date_format(date_column, '%Y-%m-%d') BETWEEN (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+0)) AND (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+6))

 

테스트

-- 2021.06.15 (화) 기준 : 성공

select true as R from DUAL

where date_format(now(), '%Y-%m-%d') BETWEEN (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+0)) AND (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+6))

 

-- 2021.06.14 (월) 기준 : 성공

select true as R from DUAL

where date_format(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') BETWEEN (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+0)) AND (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+6))

       

-- 2021.06.13 (일) 기준 : 실패

select true as R from DUAL

where date_format(DATE_ADD(now(), INTERVAL -2 DAY), '%Y-%m-%d') BETWEEN (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+0)) AND (SELECT ADDDATE(CURDATE(),-WEEKDAY(CURDATE())+6))