[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;
최신 댓글