Time / Date PL/SQL Library

Pre-Requisites:

None.

Code:

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

-- ------------------------------------------------------------------------- -- -- -- -- Title: lib_time.sql -- -- Author: Steve Roach -- -- -- -- Description: A set of library functions and procedures to support time, -- -- date and period functionality. -- -- -- -- Functions: calendar_week -- -- Returns the number of the calendar week in the year that -- -- a date falls in. -- -- calendar_qtr -- -- Returns the number of the calendar quarter in the year -- -- that a date falls in. -- -- financial_week (Australia) -- -- Returns the number of the financial week in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_month (Australia) -- -- Returns the number of the financial month in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_qtr (Australia) -- -- Returns the number of the financial quarter in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_year (Australia) -- -- Returns the number of the financial year in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- -- -- Installation: To install for production, run this script as-is. This will -- -- install the package in the target database. -- -- To install for debug, edit the 'ALTER SESSION' statement, -- -- below, changing 'debug: FALSE' to 'debug: TRUE' before -- -- running the script. -- -- -- -- ------------------------------------------------------------------------- -- -- Set flags for running debug mode: ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE'; -- Compile function. CREATE OR REPLACE PACKAGE lib_time AS FUNCTION calendar_week( p_date DATE) RETURN NUMBER; FUNCTION calendar_qtr( p_date DATE) RETURN NUMBER; FUNCTION financial_week( p_date DATE) RETURN NUMBER; FUNCTION financial_month( p_date DATE) RETURN NUMBER; FUNCTION financial_qtr( p_date DATE) RETURN NUMBER; FUNCTION financial_year( p_date DATE) RETURN NUMBER; END lib_time; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY lib_time AS -- Returns the number of the calendar week in the year that -- a date falls in. FUNCTION calendar_week( p_date DATE) RETURN NUMBER IS l_start_date DATE; l_day_count NUMBER; l_date_span NUMBER := 0; l_week_number NUMBER := 1; BEGIN $IF $$debug $THEN dbms_output.put_line('calendar_week START'); $END l_start_date := TO_DATE('02-JAN-' || TO_CHAR(p_date, 'YYYY')); l_date_span := p_date - l_start_date; $IF $$debug $THEN dbms_output.put_line('p_date = ' || p_date); dbms_output.put_line('l_start_date = ' || l_start_date); dbms_output.put_line('l_date_span = ' || l_date_span); dbms_output.put_line('-'); $END FOR l_day_count IN 0 .. l_date_span LOOP $IF $$debug $THEN dbms_output.put_line('l_day_count = ' || l_day_count); dbms_output.put_line('IF criteria = ' || TO_CHAR(l_start_date + l_day_count, 'DY')); dbms_output.put_line('-'); $END IF TO_CHAR(l_start_date + l_day_count, 'DY') = 'MON' THEN l_week_number := l_week_number + 1; $IF $$debug $THEN dbms_output.put_line('l_week_number = ' || l_week_number); $END END IF; END LOOP; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_week_number); dbms_output.put_line('calendar_week END'); dbms_output.put_line('-'); $END RETURN l_week_number; END calendar_week; -- Returns the number of the calendar quarter in the year -- that a date falls in. FUNCTION calendar_qtr( p_date DATE) RETURN NUMBER IS l_qtr NUMBER; BEGIN l_qtr := CASE TO_CHAR(p_date - 1, 'MM') WHEN '01' THEN 1 WHEN '02' THEN 1 WHEN '03' THEN 1 WHEN '04' THEN 2 WHEN '05' THEN 2 WHEN '06' THEN 2 WHEN '07' THEN 3 WHEN '08' THEN 3 WHEN '09' THEN 3 WHEN '10' THEN 4 WHEN '11' THEN 4 ELSE 4 END; RETURN l_qtr; END; -- Returns the number of the financial week in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_week( p_date DATE) RETURN NUMBER IS l_start_date DATE; l_fin_year_start_year NUMBER; l_day_count NUMBER; l_date_span NUMBER := 0; l_week_number NUMBER := 1; BEGIN $IF $$debug $THEN dbms_output.put_line('financial_week START'); dbms_output.put_line('p_date = ' || p_date); $END l_fin_year_start_year := TO_NUMBER(TO_CHAR(p_date, 'YYYY')); $IF $$debug $THEN dbms_output.put_line('l_fin_year_start_year = ' || l_fin_year_start_year); dbms_output.put_line('TO_CHAR(p_date, ''MM'') = ' || TO_CHAR(p_date, 'MM')); dbms_output.put_line( 'lib_string.ltrim_chr(TO_CHAR(p_date, ''MM''), ''0'') = ' || lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')); $END IF TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')) < 7 THEN l_fin_year_start_year := l_fin_year_start_year - 1; END IF; l_start_date := TO_DATE('02-JUL-' || TO_CHAR(l_fin_year_start_year)); l_date_span := p_date - l_start_date; $IF $$debug $THEN dbms_output.put_line('p_date = ' || p_date); dbms_output.put_line('l_fin_year_start_year = ' || l_fin_year_start_year); dbms_output.put_line('l_start_date = ' || l_start_date); dbms_output.put_line('l_date_span = ' || l_start_date); $END FOR l_day_count IN 0 .. l_date_span LOOP $IF $$debug $THEN dbms_output.put_line('l_day_count = ' || l_day_count); dbms_output.put_line('IF criteria = ' || TO_CHAR(l_start_date + l_day_count, 'DY')); $END IF TO_CHAR(l_start_date + l_day_count, 'DY') = 'MON' THEN l_week_number := l_week_number + 1; END IF; END LOOP; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_week_number); dbms_output.put_line('calendar_week END'); $END RETURN l_week_number; END financial_week; -- Returns the number of the financial month in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_month( p_date DATE) RETURN NUMBER IS l_month NUMBER; BEGIN l_month := TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date - 1, 'MM'), '0')); IF l_month > 6 THEN RETURN l_month - 6; ELSE RETURN l_month + 6; END IF; END; -- Returns the number of the financial quarter in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_qtr( p_date DATE) RETURN NUMBER IS l_qtr NUMBER; BEGIN l_qtr := CASE TO_CHAR(p_date - 1, 'MM') WHEN '01' THEN 3 WHEN '02' THEN 3 WHEN '03' THEN 3 WHEN '04' THEN 4 WHEN '05' THEN 4 WHEN '06' THEN 4 WHEN '07' THEN 1 WHEN '08' THEN 1 WHEN '09' THEN 1 WHEN '10' THEN 2 WHEN '11' THEN 2 ELSE 2 END; RETURN l_qtr; END; -- Returns the number of the financial year in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_year( p_date DATE) RETURN NUMBER IS BEGIN IF TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')) > 6 THEN RETURN TO_NUMBER(TO_CHAR(p_date, 'YYYY')) + 1; ELSE RETURN TO_NUMBER(TO_CHAR(p_date, 'YYYY')); END IF; END; END lib_time; / SHOW ERRORS