SQL snipets

Query to get comma separated values into each row

SELECT TRIM(regexp_substr(:l_comma_code,'[^,]+', 1, level)) Comma_col_value

                           FROM dual

                     CONNECT BY regexp_substr(:l_comma_code,'[^,]+', 1, level) is not null;

=>Query to get all dates in a month for a given date:

    SELECT  TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 AS day_in_month

    FROM  dual CONNECT BY 

    TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 <= LAST_DAY(TO_DATE('2024-01-01', 'YYYY-MM-DD'));

=>SQL to get ISO week and month for date:

select TO_CHAR(SYSDATE, 'IW') AS wk_Bucket, TO_CHAR(SYSDATE, 'MM') AS MN_Bucket

 FROM DUAL;

=>SQL query to get last ISO week for a given month and year

SELECT TO_CHAR(LAST_DAY(TO_DATE(:month_num || '-' || :year_num, 'MM-YYYY')), 'IW') AS last_iso_week FROM DUAL;