MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Seeding a Calendar Table

with 3 comments

While working on one of the labs, my students wanted a quick way to seed their CALENDAR table. Here’s a small script to seed twenty years of a calendar.

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'MOCK_CALENDAR') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE mock_calendar
( short_month  VARCHAR2(3)
, long_month   VARCHAR2(9)
, start_date   DATE
, end_date     DATE );
 
-- Seed the table with 10 years of data.
DECLARE
  -- Create local collection data types.
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  long_month  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');
 
  -- Declare base dates.
  start_date DATE := '01-JAN-79';
  end_date   DATE := '31-JAN-79';
 
  -- Declare years.
  years      NUMBER := 20;
 
BEGIN
 
  -- Loop through years and months.
  FOR i IN 1..years LOOP
    FOR j IN 1..short_month.COUNT LOOP
      INSERT INTO mock_calendar VALUES
      ( short_month(j)
      , long_month(j)
      , add_months(start_date,(j-1)+(12*(i-1)))
      , add_months(end_date,(j-1)+(12*(i-1))));
    END LOOP;
  END LOOP;
 
END;
/
 
-- Format set break for output.
SET PAGESIZE 16
 
-- Format column output.
COL short_month FORMAT A5 HEADING "Short|Month"
COL long_month  FORMAT A9 HEADING "Long|Month"
COL start_date  FORMAT A9 HEADING "Start|Date"
COL end_date    FORMAT A9 HEADING "End|Date" 
 
SELECT * FROM mock_calendar;

The output will look like this with a page break by year:

Short Long      Start     End
Month Month     Date      Date
----- --------- --------- ---------
JAN   January   01-JAN-79 31-JAN-79
FEB   February  01-FEB-79 28-FEB-79
MAR   March     01-MAR-79 31-MAR-79
APR   April     01-APR-79 30-APR-79
MAY   May       01-MAY-79 31-MAY-79
JUN   June      01-JUN-79 30-JUN-79
JUL   July      01-JUL-79 31-JUL-79
AUG   August    01-AUG-79 31-AUG-79
SEP   September 01-SEP-79 30-SEP-79
OCT   October   01-OCT-79 31-OCT-79
NOV   November  01-NOV-79 30-NOV-79
DEC   December  01-DEC-79 31-DEC-79

Written by maclochlainn

November 18th, 2009 at 5:38 pm

Posted in Oracle,pl/sql,sql

3 Responses to 'Seeding a Calendar Table'

Subscribe to comments with RSS or TrackBack to 'Seeding a Calendar Table'.

  1. I tried to submit a comment but it looks like it’s just disappeared. If it went through to moderation then apologies, I’ve probably submitted it twice.

    Dominic Brooks

    19 Nov 09 at 3:23 am

  2. Took me a while to read the guidelines on submitting a code comment.
    Here goes:

    INSERT INTO mock_calendar
    WITH seeds AS
    (SELECT to_date('01-JAN-1979','DD-MON-YYYY') AS start_date
     ,      20 AS years
     FROM    dual)
    , dates AS
    (SELECT add_months(start_date,(rownum)-1) AS month_start
     FROM   seeds s
     CONNECT BY rownum <= s.years * 12)
    SELECT TO_CHAR(month_start,'MON') AS short_month
    ,      TO_CHAR(month_start,'Month') AS long_month
    ,      month_start AS start_date
    ,      add_months(month_start,1) - 1 AS  end_date
    FROM   dates
    ORDER BY month_start;

    Dominic Brooks

    19 Nov 09 at 3:25 am

  3. Dominic, I apologize for the inconvenience. You can mail the code to me next time by using the Contact Me form that I built. I went and captured your entry from spam. BTW, that’s an awesome SQL-only solution for Oracle. :-)

    maclochlainn

    19 Nov 09 at 9:24 am

Leave a Reply