develog

[mysql] 달력 출력 본문

카테고리 없음

[mysql] 달력 출력

냐옴 2023. 11. 13. 22:34

 

이번 달 달력 출력

with recursive
T as (
    select last_day(curdate() - interval 1 month) + interval 1 day as startDate
    union all
    select startDate + interval 1 day from T where last_day(curdate()) > startDate
)
select *
from T
;

 

특정 달 달력 출력

with recursive
T as (
    select last_day(str_to_date('20240201', '%Y%m%d') - interval 1 month) + interval 1 day as startDate
    union all
    select startDate + interval 1 day from T where last_day(str_to_date('20240201', '%Y%m%d')) > startDate
)
select *
from T
;

 

시작일, 종료일 사이에 날짜 구하기

set @start_dt = '20240311';
set @end_dt = '20240320';

WITH RECURSIVE 
cal_dates AS (
  SELECT 
    DATE_FORMAT(@start_dt, '%Y-%m-%d') AS cal_dt
  UNION
  SELECT 
    DATE_FORMAT(DATE_ADD(cal_dt, INTERVAL 1 DAY), '%Y-%m-%d')
  FROM cal_dates
  WHERE date_format(DATE_ADD(cal_dt, INTERVAL 1 DAY), '%Y%m%d') <= @end_dt
)
SELECT * 
FROM cal_dates
;
저작자표시 비영리 동일조건 (새창열림)
Comments