728x90
query
SELECT DATE_FORMAT(A.DATE,'%Y-%m-%d') AS DATE, FN_GET_DAYOFWEEK(A.DATE) AS WEEK_NM
FROM (
SELECT CURDATE() - INTERVAL (A.A + (10 * B.A) + (100 * C.A) + (1000 * D.A) ) DAY AS DATE
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
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 D
) A
WHERE DATE_FORMAT(A.DATE,'%Y-%m') = '2022-04'
ORDER BY A.DATE ASC
FN_GET_DAYOFWEEK
CREATE DEFINER=`bb_table`@`%` FUNCTION `bb_table`.`FN_GET_DAYOFWEEK`(V_DT DATETIME
) RETURNS varchar(10) CHARSET utf8
BEGIN
DECLARE V_RSLT VARCHAR(10);
DECLARE V_WEEK INT(10);
SELECT DAYOFWEEK(V_DT) INTO V_WEEK;
IF V_WEEK = 1 THEN
SET V_RSLT = '(일)';
ELSEIF V_WEEK = 2 THEN
SET V_RSLT = '(월)';
ELSEIF V_WEEK = 3 THEN
SET V_RSLT = '(화)';
ELSEIF V_WEEK = 4 THEN
SET V_RSLT = '(수)';
ELSEIF V_WEEK = 5 THEN
SET V_RSLT = '(목)';
ELSEIF V_WEEK = 6 THEN
SET V_RSLT = '(금)';
ELSEIF V_WEEK = 7 THEN
SET V_RSLT = '(토)';
END IF;
RETURN V_RSLT;
END
FN_GET_DAYOFWEEK
728x90