[SOLVED] SQL Function to select first day of current year and everyday after

SQL Function to select first day of current year and everyday after

I'm looking to get a quick and efficient code to get the first day of current year and everyday after that and their week numbers. So far I have something like this to start me off:

SELECT 
to_char(TRUNC(SysDate,'YEAR'), 'WW')Week_No,
to_char(TRUNC(SysDate,'YEAR'), 'DD/MM/YY')First_Day

FROM dual;

Which gets me week number and first day of current year. I also have this to get me the last calendar day of current year:

SELECT ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 from dual;

I am being extremely silly or maybe it's end of the day brain mush but I'm trying to get the bit in the middle now. The point of this is to get a list of each calendar day and week number.

If anyone can guide me here I'd appreciate this.

Thanks

Solution

Are you looking for something like this:

SELECT
    ST_DT + LEVEL - 1,
    TO_CHAR(ST_DT + LEVEL - 1, 'IW') AS DY
FROM
    (
        SELECT
            TRUNC(SYSDATE, 'YEAR') ST_DT,
            TRUNC(SYSDATE, 'YEAR') + INTERVAL '12' MONTH - INTERVAL '1' DAY END_DT
        FROM DUAL
    )
CONNECT BY LEVEL <= END_DT - ST_DT
ORDER BY LEVEL

Cheers!!