sql - Get day name oracle query

Question

How get day name in below example:

SELECT
    day   AS return,
    day   AS display,
    ''   AS day_name
FROM
    (
        WITH temp ( col ) AS (
            SELECT
                to_date(2, 'mm') --2 is February
            FROM
                dual
        )
        SELECT
            to_number(to_char(col + level - 1, 'dd')) day
        FROM
            temp
        CONNECT BY
            level <= last_day(col) - col + 1
        ORDER BY
            day
    )

question from:https://stackoverflow.com/questions/65915895/get-day-name-oracle-query

Answer 1

You may need the following:

SELECT
    day   AS return,
    day   AS display,
    day_name  AS day_name
FROM
    (
        WITH temp ( col ) AS (
            SELECT
                to_date(2, 'mm') --2 is February
            FROM
                dual
        )
        SELECT
            to_number(to_char(col + level - 1, 'dd')) day,
            to_char(col + level - 1, 'DAY') as day_name     /* day/DAY/Day for lower/upper case*/
        FROM
            temp
        CONNECT BY
            level <= last_day(col) - col + 1
        ORDER BY
            day
    )