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

+ Recent posts