CODEWITHSUNDEEP
sqldatabaseoracle-database
terrific
terrific

Reputation: 1667

How to populate calendar table in Oracle?

I want to maintain a calender table in Oracle DB which I want to populate with all the days of the year starting from 2011 to 2013 (it may be till any year). How can I do that?

Consider my DB table has columns and example dataset is:

S.No  Cal_Dt      DayName 
1     01-01-2011  Monday
2     02-01-2011  Tuesday
3     03-01-2011  Wednesday

and so on.

I am more concerned with the Cal_Dt only here (DayName is optional).

Upvotes: 8

Views: 36630

Answers (4)

Ridwanul Islam
Ridwanul Islam

Reputation: 36

CREATE TABLE CALENDAR (
CALENDAR_DATE     DATE,
CALENDAR_DAY      VARCHAR2(10),
CALENDAR_MONTH    VARCHAR2(10),
CALENDAR_QUARTER  VARCHAR2(10),
CALENDAR_YEAR     VARCHAR2(10),
DAY_OF_WEEK_NUM   NUMBER(1),
DAY_OF_WEEK_NAME  VARCHAR2(10),
DATE_NUM          VARCHAR2(10),
QUARTER_CD        VARCHAR2(10),
MONTH_NAME_CD     VARCHAR2(10),
FULL_MONTH_NAME   VARCHAR2(10),
HOLIDAY_NAME      VARCHAR2(50),
HOLIDAY_FLAG      NUMBER(1) 
);

After Creating Table Please execute following Script:

DECLARE
           START_DATE DATE;
           END_DATE DATE;
       BEGIN
           START_DATE := TO_DATE('01/01/2025', 'DD/MM/YYYY');
           END_DATE := START_DATE + 365;
       
           WHILE START_DATE <= END_DATE LOOP
               INSERT INTO CALENDAR (
                   CALENDAR_DATE,
                   CALENDAR_DAY,
                   CALENDAR_MONTH,
                   CALENDAR_QUARTER,
                   CALENDAR_YEAR,
                   DAY_OF_WEEK_NUM,
                   DAY_OF_WEEK_NAME,
                   DATE_NUM,
                   QUARTER_CD,
                   MONTH_NAME_CD,
                   FULL_MONTH_NAME,
                   HOLIDAY_NAME,
                   HOLIDAY_FLAG
               ) VALUES (
                   START_DATE,
                   TO_CHAR(START_DATE, 'DD'),
                   TO_CHAR(START_DATE, 'MM'),
                   TO_CHAR(START_DATE, 'Q'),
                   TO_CHAR(START_DATE, 'YYYY'),
                   CASE WHEN TO_CHAR(START_DATE, 'D') = '1' THEN 7 ELSE TO_CHAR(START_DATE, 'D') - 1 END,
                   TO_CHAR(START_DATE, 'DY'),
                   TO_CHAR(START_DATE, 'YYYYMMDD'),
                   TO_CHAR(START_DATE, 'YYYY') || 'Q' || TO_CHAR(START_DATE, 'Q'),
                   TO_CHAR(START_DATE, 'MON'),
                   TO_CHAR(START_DATE, 'MONTH'),
                   NULL,
                   0
               );
       
               START_DATE := START_DATE + 1;
           END LOOP;
       END;

Upvotes: 0

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

This is a simple and easy way to do it

with calendar as (
        select :startdate + rownum - 1 as day
        from dual
        connect by rownum < :enddate - :startdate
    )
select rownum as "S.No", to_date(day,'dd_mm_yyyy') as "Cal_Dt", to_char(day,'day') as "DayName"
from calendar

Upvotes: 15

Ryan
Ryan

Reputation: 91

with calendar as (
        select rownum - 1 as daynum
        from dual
        connect by rownum < sysdate - to_date('1-jan-2010') + 1
    )
select to_date('1-jan-2010') + daynum as monthdate
from calendar
;

Upvotes: 6

Ben
Ben

Reputation: 52863

declare
  v_date date := to_date('20110101','yyyymmdd');
begin

   while v_date < sysdate + 720 loop

      insert into calender
      values ( v_date, to_char(v_date,'DAY'));

      v_date := v_date + 1;

   end loop;
   commit;

end;
/

This is not best practice and you should use Allesandro Rossi's solution. This may only be useful if you're using Oracle 9i or earlier and populating a large table.

Upvotes: 2

Related Questions