출처 : 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';






+ Recent posts