[mysql] 시스템 날짜 출력하기, MAKEDATE()

오늘 날짜 출력

SELECT MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW())); -- 2023-05-10

 

세 달 전 날짜 출력

select DATE_ADD(MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW())), INTERVAL -3 MONTH)

 

LAST_DAY() : date타입 인자값이 속한 월의 마지막 날짜를 출력

SELECT LAST_DAY(NOW() - INTERVAL 3 MONTH)

 

지난 2달 ~ 이번달의 모든 날짜 출력

 SELECT LAST_DAY(NOW() - INTERVAL 3 MONTH) + INTERVAL 1 DAY + INTERVAL n.n DAY AS date
      FROM (
         SELECT a.N + b.N * 10 + c.N * 100 AS n
         FROM (SELECT 0 AS N 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 N 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 N 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
         WHERE a.N + b.N * 10 + c.N * 100 < DATEDIFF(DATE_ADD(MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW())), INTERVAL 3 MONTH), MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW())))      
      ) AS n
  order by date