PL/SQL Calendar

From Oracle FAQ
Jump to: navigation, search

Basic part of the PL/SQL Calendar consist of three functions:

  • IS_LEAP_YEAR [Finds the year is leap year or not]
  • DAYS_IN_A_MONTH [Finds the no of days in a given month]
  • SQLCAL [Returns the full calendar]

Leap year[edit]

First, IS_LEAP_YEAR - This is written to find out the given year is leap year or not.

Leap year logic - Step A -

Is it divisible by 4? 
---------------------------------------------------------------
  YES                                                     | NO
  Is Divisible by 100?                                    |
---------------------------------------------------------------
  YES                             |     NO                |
  Is Divisible by 400?            | (These are Leap Year) |
---------------------------------------------------------------
  YES                  |    NO    |                       |
(These are Leap Year)  |          |                       |
---------------------------------------------------------------

In PL/SQL Code it can be written as -

FUNCTION is_leap_year (p_n_year IN NUMBER)
  RETURN BOOLEAN
IS
  p_b_leap_year   BOOLEAN := FALSE;
BEGIN
  IF     (MOD (p_n_year, 4) = 0)
     AND ((MOD (p_n_year, 100) <> 0) OR (MOD (p_n_year, 400) = 0))
  THEN
     p_b_leap_year := TRUE;
  END IF;

  RETURN p_b_leap_year;
END;

In SQL, it is easy to find if a year is leap using Oracle date functions and checking if 31 December is the 366th day of the year:

DECODE(TO_CHAR(TO_DATE('31/12'||year,'DD/MM/YYYY'),'DDD'), '366','TRUE', 'FALSE')

Here's an example:

SQL> WITH years AS (SELECT 1994+level year FROM dual CONNECT BY LEVEL <= 10)
  2  SELECT year,
  3         DECODE(TO_CHAR(TO_DATE('31/12'||year,'DD/MM/YYYY'),'DDD'),
  4                '366','TRUE', 'FALSE') "LEAP?"
  5  FROM years
  6  /
      YEAR LEAP?
---------- -----
      1995 FALSE
      1996 TRUE
      1997 FALSE
      1998 FALSE
      1999 FALSE
      2000 TRUE
      2001 FALSE
      2002 FALSE
      2003 FALSE
      2004 TRUE

Days in month[edit]

Every month in the Gregorian Calendar has a fixed number of days except February which has 29 days in a leap year and 28 days in a non-leap year. Jan, Mar, May, Jul, Aug, Oct, Dec have 31 days. Apr, Jun, Sep, Nov have 30 days.

A function with a simple case statement can be written for this -

FUNCTION days_in_a_month (p_n_month IN NUMBER, p_n_year IN NUMBER)
  RETURN NUMBER
IS
  days_in_month   NUMBER (2);
BEGIN
  CASE
     WHEN (   p_n_month = 1
           OR p_n_month = 3
           OR p_n_month = 5
           OR p_n_month = 7
           OR p_n_month = 8
           OR p_n_month = 10
           OR p_n_month = 12
          )
     THEN
        days_in_month := 31;
     WHEN p_n_month = 2
     THEN
        IF (is_leap_year (p_n_year))
        THEN
           days_in_month := 29;
        ELSE
           days_in_month := 28;
        END IF;
     ELSE
        days_in_month := 30;
  END CASE;

  RETURN days_in_month;
END;

In SQL, it can be done just returning the number of the last day of the month:

TO_CHAR(LAST_DAY(TO_DATE(month||'/'||year,'MM/YYYY')),'DD')

Here's an example:

SQL> WITH 
  2    months AS (
  3      SELECT 1994+level year,
  4             level month
  5      FROM dual
  6      CONNECT BY LEVEL <= 12
  7    )
  8  SELECT year, month,
  9         TO_CHAR(LAST_DAY(TO_DATE(month||'/'||year,'MM/YYYY')),'DD') NB_DAYS
 10  FROM months
 11  /
      YEAR      MONTH NB
---------- ---------- --
      1995          1 31
      1996          2 29
      1997          3 31
      1998          4 30
      1999          5 31
      2000          6 30
      2001          7 31
      2002          8 31
      2003          9 30
      2004         10 31
      2005         11 30
      2006         12 31

Print complete calendar[edit]

Now, the main part which will return the calendar is SQLCAL but before that we need to create a type assign_Days and ret_assign_days to make this function to pipelined.

So, type assign_Days can be defined as -

TYPE assign_Days IS OBJECT (
       Monday      varchar2(10), 
       Tuesday     varchar2(10),
       Wednesday   varchar2(10),
       Thursday    varchar2(10),
       Friday      varchar2(10),
       Saturday    varchar2(10),
       Sunday      varchar2(10));
TYPE ret_assign_days IS TABLE OF assign_days;

SQLCAL Code -

FUNCTION SQLCAL (p_n_month IN NUMBER, p_n_year IN NUMBER)
  RETURN ret_assign_days
                        PIPELINED
IS
  p_n_day          NUMBER (2)    := 14;
  p_d_date         DATE;     -- For storing a date for that perticular month
  ln_day_name      VARCHAR2 (10);                       -- To Store Day Name
  days_in_mon      NUMBER (2);         -- To Store No of the Days in a month
  start_position   NUMBER (1);   -- To calculate Start Position of the Month
  p_b_flag         BOOLEAN       := TRUE;            -- Flag for calculation
  p_n_day_cnt      NUMBER (2)    := 0;          -- Counter to count the days
BEGIN
  p_d_date :=
      TO_DATE (p_n_day || '/' || p_n_month || '/' || p_n_year, 'dd/mm/yyyy');

  -- Retrieves the First Day Name of the Month
  SELECT TO_CHAR (LAST_DAY (p_d_date - TO_CHAR (p_d_date, 'dd')) + 1, 'Day'),
         days_in_a_month (p_n_month, p_n_year)
    INTO ln_day_name,
         days_in_mon
    FROM DUAL;

  -- Makes the position of the Start Day
  CASE TRIM (ln_day_name)
     WHEN 'Monday'
     THEN
        start_position := 0;
     WHEN 'Tuesday'
     THEN
        start_position := 1;
     WHEN 'Wednesday'
     THEN
        start_position := 2;
     WHEN 'Thursday'
     THEN
        start_position := 3;
     WHEN 'Friday'
     THEN
        start_position := 4;
     WHEN 'Saturday'
     THEN
        start_position := 5;
     WHEN 'Sunday'
     THEN
        start_position := 6;
     ELSE
        start_position := 0;
  END CASE;

  FOR i IN 1 .. 6
  LOOP
     IF p_b_flag
     THEN
        CASE TRIM (ln_day_name)
           WHEN 'Monday'
           THEN
              PIPE ROW (assign_days ('1', '2', '3', '4', '5', '6', '7'));
              p_n_day_cnt := 7;
           WHEN 'Tuesday'
           THEN
              PIPE ROW (assign_days (NULL, '1', '2', '3', '4', '5', '6'));
              p_n_day_cnt := 6;
           WHEN 'Wednesday'
           THEN
              PIPE ROW (assign_days (NULL, NULL, '1', '2', '3', '4', '5'));
              p_n_day_cnt := 5;
           WHEN 'Thursday'
           THEN
              PIPE ROW (assign_days (NULL, NULL, NULL, '1', '2', '3', '4'));
              p_n_day_cnt := 4;
           WHEN 'Friday'
           THEN
              PIPE ROW (assign_days (NULL, NULL, NULL, NULL, '1', '2', '3'));
              p_n_day_cnt := 3;
           WHEN 'Saturday'
           THEN
              PIPE ROW (assign_days (NULL, NULL, NULL, NULL, NULL, '1', '2'));
              p_n_day_cnt := 2;
           WHEN 'Sunday'
           THEN
              PIPE ROW (assign_days (NULL, NULL, NULL, NULL, NULL, NULL,
                                     '1'));
              p_n_day_cnt := 1;
           ELSE
              NULL;
        END CASE;

        p_b_flag := FALSE;
     ELSIF i > 4
     THEN
        IF (p_n_day_cnt + 7) > days_in_mon
        THEN
           IF (days_in_mon - p_n_day_cnt) > 7
           THEN
              PIPE ROW (assign_days (p_n_day_cnt + 1,
                                     p_n_day_cnt + 2,
                                     p_n_day_cnt + 3,
                                     p_n_day_cnt + 4,
                                     p_n_day_cnt + 5,
                                     p_n_day_cnt + 6,
                                     p_n_day_cnt + 7
                                    ));
              p_n_day_cnt := p_n_day_cnt + 7;
           ELSE
              CASE (days_in_mon - p_n_day_cnt)
                 WHEN 6
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           p_n_day_cnt + 2,
                                           p_n_day_cnt + 3,
                                           p_n_day_cnt + 4,
                                           p_n_day_cnt + 5,
                                           p_n_day_cnt + 6,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 6;
                 WHEN 5
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           p_n_day_cnt + 2,
                                           p_n_day_cnt + 3,
                                           p_n_day_cnt + 4,
                                           p_n_day_cnt + 5,
                                           NULL,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 5;
                 WHEN 4
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           p_n_day_cnt + 2,
                                           p_n_day_cnt + 3,
                                           p_n_day_cnt + 4,
                                           NULL,
                                           NULL,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 4;
                 WHEN 3
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           p_n_day_cnt + 2,
                                           p_n_day_cnt + 3,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 3;
                 WHEN 2
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           p_n_day_cnt + 2,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 2;
                 WHEN 1
                 THEN
                    PIPE ROW (assign_days (p_n_day_cnt + 1,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL
                                          ));

                    p_n_day_cnt := p_n_day_cnt + 1;
                 ELSE
                    PIPE ROW (assign_days (NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL
                                          ));
              END CASE;
           END IF;
        ELSE
           PIPE ROW (assign_days (p_n_day_cnt + 1,
                                  p_n_day_cnt + 2,
                                  p_n_day_cnt + 3,
                                  p_n_day_cnt + 4,
                                  p_n_day_cnt + 5,
                                  p_n_day_cnt + 6,
                                  p_n_day_cnt + 7
                                 ));
           p_n_day_cnt := p_n_day_cnt + 7;
        END IF;
     ELSE
        PIPE ROW (assign_days (p_n_day_cnt + 1,
                               p_n_day_cnt + 2,
                               p_n_day_cnt + 3,
                               p_n_day_cnt + 4,
                               p_n_day_cnt + 5,
                               p_n_day_cnt + 6,
                               p_n_day_cnt + 7
                              ));
        p_n_day_cnt := p_n_day_cnt + 7;
     END IF;
  END LOOP;
END sqlcal;

That's all - You can now query the function to retrieve the calendar.

For example - To retrieve the August, 2010 Calendar you can query like the below -

SELECT * FROM TABLE (sqlcal ('09', '2010'))
/

Several SQL ways have been posted in Forum topic Create A Calendar for the Given Month and Year