출처 : https://stackoverflow.com/questions/2157282/generate-days-from-date-range
-- STR_TO_DATE 파라미터에 들어가는 날짜 역순으로 해서 최대 1000개 row를 생성하여 조회조건으로 걸러낸다.
SELECT DT.YYYYMMDD
FROM (SELECT STR_TO_DATE('20170914', '%Y%m%d') - INTERVAL(A.A + (10 * B.A) + (100 * C.A)) DAY AS YYYYMMDD
-- SELECT DATE_ADD(STR_TO_DATE('20170914', '%Y%m%d'), INTERVAL -(A.A + (10 * B.A) + (100 * C.A)) DAY) YYYYMMDD
, A.A AS AA
, B.A AS BA
, C.A AS CA
FROM (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS A
CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS B
CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS C
) DT
WHERE DT.YYYYMMDD BETWEEN '20170801' AND '20170914';
|
-- 현재 날짜 역순으로 해서 최대 1000개 row를 생성하여 조회조건으로 걸러낸다.
SELECT DT.YYYYMMDD
FROM (SELECT DATE_ADD(NOW(), INTERVAL -(A.A + (10 * B.A) + (100 * C.A)) DAY) YYYYMMDD
, A.A AS AA
, B.A AS BA
, C.A AS CA
FROM (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION
ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS A
CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION
ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS B
CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION
ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS C
) DT
WHERE DT.YYYYMMDD BETWEEN '20170801' AND '20170914';
|