Oracle 11g - For Loop That Inserts Only Weekdays Into A Table?

I want to insert some data into a table associated with dates for the next year. I actually only need workdays inserted. BEGIN FOR i IN 1..365 LOOP INSERT INTO MY_TABLE (ID

Solution 1:

You could always check the day of the week before inserting the row (the names of the days of the week will depend on your NLS settings so this isn't the most robust solution possible)

BEGINFOR i IN1..365 LOOP
    IF( to_char(sysdate-1+i,'fmDAY') NOTIN ('SATURDAY', 'SUNDAY') )
    THENINSERTINTO MY_TABLE (ID, MY_DATE)
        VALUES (i, (to_date(sysdate,'DD-MON-YY')-1)+i);
    END IF;
  END LOOP;
 END;

Solution 2:

I would suggest using to_date(your_date,'d') as @Jeff Moore mentions. However, I'd also suggest getting rid of the for..loop. As a bonus, this will add all days of any given year, unlike your version, which will generate an extra day on leap years:

INSERTINTO MY_TABLE (ID, MY_DATE)
SELECT   lvl, dt
  FROM   (    SELECT   LEVEL lvl,
                       TO_DATE('1/1/2011', 'mm/dd/yyyy') + LEVEL -1 dt
                FROM   DUAL
          CONNECTBY   TO_DATE('1/1/2011', 'mm/dd/yyyy') + LEVEL -1<
                          ADD_MONTHS(TO_DATE('1/1/2011', 'mm/dd/yyyy'), 12))
 WHERE   TO_CHAR(dt, 'd') NOTIN (1, 7)

If you want your "ID" column to be contiguous, you can use rownum instead of lvl in the outer query.

Solution 3:

You can use one of the following date formats to check which day it is.

select to_char(sysdate,'DAY') from dual; /* TUESDAY */ select to_char(sysdate,'D') from dual; /* 3 */ select to_char(sysdate,'DY') from dual; /* TUE */

Add the if statement as shown below to remove days that equal SAT or SUN.

BEGINFOR i IN1..365 LOOP
  IF to_char(sysdate-1+i,'DY') NOTin ('SAT','SUN') THENINSERTINTO MY_TABLE (ID, MY_DATE) VALUES (i, (to_date(sysdate,'DD-MON-YY')-1)+i);
  END IF;
 END LOOP;
END;

Solution 4:

If you let Monday = 0 and Sunday = 6 you could use (if mod(i,7) < 4 )) then Insert... should work.