PL/SQL Calendar
From Oracle FAQ
⧼orafaq-jumptonavigation⧽⧼orafaq-jumptosearch⧽
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
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
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
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
