[mysql] 현재시각에서 이전 일요일, 다음 일요일 구하기

요일은 상황에 맞게 변경하여 구할 수 있음
-- 일요일 구하기
SELECT DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(DATE(NOW())) - 1 DAY) CURR_SUNDAY
     , DATE_ADD(DATE(NOW()), INTERVAL 8 - DAYOFWEEK(DATE(NOW())) DAY) NEXT_SUNDAY;

SET @DAYS = 5; -- 목요일 구하기
SELECT DATE_SUB(NOW(), INTERVAL DAYOFWEEK(NOW()) - @DAYS  - 1 DAY) curr_day
     , DATE_ADD(NOW(), INTERVAL 7 + @DAYS - DAYOFWEEK(NOW()) DAY) next_day
  FROM DUAL;
    
함수를 사용하면 더 편리하다.
-- 오라클 next_day 함수와 동일함
DROP FUNCTION IF EXISTS NEXT_DAY;
CREATE FUNCTION NEXT_DAY(PI_DATE DATETIME, PI_DAY TINYINT(1)) RETURNS DATETIME
RETURN CASE WHEN PI_DAY = 1 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 8 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 2 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 9 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 3 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 10 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 4 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 11 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 5 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 12 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 6 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 13 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
            WHEN PI_DAY = 7 THEN DATE_ADD(PI_DATE, INTERVAL IF(PI_DAY < DAYOFWEEK(PI_DATE), 14 - DAYOFWEEK(PI_DATE), PI_DAY - DAYOFWEEK(PI_DATE)) DAY) 
       END;
    

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다