自己写的关于日期处理的包

-------------------------------------------------

-- Export file for user COMMON                 --

-- Created by Libingfeng on 2005-6-10, 8:48:40 --

-------------------------------------------------

 

spool Common.PKGDate.log

 

prompt

prompt Creating package PKGDATE

prompt ========================

prompt

CREATE OR REPLACE PACKAGE PKGDATE AS

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  *                        类型定义

  * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    TYPE TCDate IS TABLE OF DATE; -- 日期集合

 

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  *                        常量定义

  * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    CSTComma           CONSTANT CHAR(1) := ','; -- 逗号

    CSTDateFmt         CONSTANT CHAR(10) := 'YYYY-MM-DD'; -- 日期格式

    CSTTimeFmt         CONSTANT CHAR(10) := 'HH24:MI:SS'; -- 时间格式

    CSTDateTimeFmt     CONSTANT CHAR(21) := 'YYYY-MM-DD HH24:MI:SS'; -- 日期时间格式

    CSTSimpleTimeFmt   CONSTANT CHAR(10) := 'HH24:MI'; -- 简短时间格式

    CSTTag             CONSTANT CHAR(6) := '#TAG#'; -- 特殊标签

    CSTSecondsOfOneDay CONSTANT FLOAT := 1 / (24 * 60 * 60); -- 每天的秒数

    CSTCnweeks         CONSTANT VARCHAR2(14) := '日一二三四五六'; -- 中文星期

    CSTDateSplit       CONSTANT CHAR(1) := '-'; -- 日期分隔符

    CSTTimeSplit       CONSTANT CHAR(1) := ':'; -- 时间分隔符

    CSTSpace           CONSTANT CHAR(1) := ' '; -- 空格

 

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  *                        变量定义

  * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    gvNow   DATE := SYSDATE;

    gvToday DATE := TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD');

 

    FUNCTION NOW RETURN DATE;

    FUNCTION TODAY RETURN DATE;

    FUNCTION YEAR(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION YEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION SEASON(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION SEASON(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION MONTH(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION MONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION DAY(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION DAY(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION DAYOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION DAYOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION HOUR(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION HOUR(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION MINUTE(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION MINUTE(D DATE) RETURN INTEGER;

    FUNCTION SECOND(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION SECOND(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION WEEK(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION WEEKEX(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION CNWEEK(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION WEEKOFMONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION WEEKOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION TODATE

    (

       Y INTEGER,

       M INTEGER DEFAULT 1,

       D INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION TODATE(YMD VARCHAR2) RETURN DATE;

    FUNCTION TODATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION TODATETIME

    (

       Y INTEGER,

       M INTEGER,

       D INTEGER,

       H INTEGER,

       I INTEGER,

       S INTEGER

    ) RETURN DATE;

    FUNCTION TODATETIME(YMDHMS VARCHAR2) RETURN DATE;

    FUNCTION TODATETIME(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION TOTIME

    (

       H INTEGER,

       I INTEGER,

       S INTEGER

    ) RETURN DATE;

    FUNCTION TOTIME(HMS VARCHAR2) RETURN DATE;

    FUNCTION TOTIME(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION TOCHAR(DATESTRING VARCHAR2) RETURN VARCHAR2;

    FUNCTION TOCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TODATECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TODATETIMECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TOTIMECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TODATESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TODATETIMESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION TOTIMESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION YEARFIRSTDATE(Y INTEGER) RETURN DATE;

    FUNCTION YEARFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION YEARFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION SEASONFIRSTDATE

    (

       Y INTEGER,

       S INTEGER

    ) RETURN DATE;

    FUNCTION SEASONFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION SEASONFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION MONTHFIRSTDATE

    (

       Y INTEGER,

       M INTEGER

    ) RETURN DATE;

    FUNCTION MONTHFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION MONTHFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION DATESTARTTIME(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION DATESTARTTIME(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION YEARLASTDATE(Y INTEGER) RETURN DATE;

    FUNCTION YEARLASTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION YEARLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION SEASONLASTDATE

    (

       S INTEGER,

       Y INTEGER

    ) RETURN DATE;

    FUNCTION SEASONLASTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION SEASONLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION MONTHLASTDATE

    (

       Y INTEGER,

       M INTEGER

    ) RETURN DATE;

    FUNCTION MONTHLASTDATE(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION MONTHLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION DATEENDTIME(DATESTRING VARCHAR2) RETURN DATE;

    FUNCTION DATEENDTIME(D DATE DEFAULT SYSDATE) RETURN DATE;

    FUNCTION DAYSBETWEEN(D1 DATE) RETURN INTEGER;

    FUNCTION DAYSBETWEEN(D1 VARCHAR2) RETURN INTEGER;

    FUNCTION DAYSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER;

    FUNCTION DAYSBETWEEN

    (

       D1 DATE,

       D2 DATE

    ) RETURN INTEGER;

    FUNCTION WEEKSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER;

    FUNCTION WEEKSBETWEEN

    (

       D1 DATE DEFAULT SYSDATE,

       D2 DATE DEFAULT SYSDATE

    ) RETURN INTEGER;

    FUNCTION MONTHSBETWEEN(D1 DATE) RETURN INTEGER;

    FUNCTION MONTHSBETWEEN(D1 VARCHAR2) RETURN INTEGER;

    FUNCTION MONTHSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER;

    FUNCTION MONTHSBETWEEN

    (

       D1 DATE,

       D2 DATE

    ) RETURN INTEGER;

    FUNCTION YEARSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER;

    FUNCTION YEARSBETWEEN

    (

       D1 DATE DEFAULT SYSDATE,

       D2 DATE DEFAULT SYSDATE

    ) RETURN INTEGER;

    FUNCTION DAYSOFYEAR(Y INTEGER) RETURN INTEGER;

    FUNCTION DAYSOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION DAYSOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION DAYSOFMONTH

    (

       Y INTEGER,

       M INTEGER

    ) RETURN INTEGER;

    FUNCTION DAYSOFMONTH(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION DAYSOFMONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER;

    FUNCTION MONTHDATECOLLECTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN TCDATE;

    FUNCTION MONTHDATECOLLECTION(D DATE) RETURN TCDATE;

    FUNCTION MONTHDATECAPTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN VARCHAR2;

    FUNCTION MONTHDAYCAPTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN VARCHAR2;

    FUNCTION WEEKOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION WEEKOFMONTH(DATESTRING VARCHAR2) RETURN INTEGER;

    FUNCTION YEARWEEKDATE

    (

       Y INTEGER,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION YEARWEEKDATE

    (

       D VARCHAR2,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION YEARWEEKDATE

    (

       D DATE DEFAULT SYSDATE,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION MONTHWEEKDATE

    (

       Y INTEGER,

       M INTEGER,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION MONTHWEEKDATE

    (

       D VARCHAR2,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION MONTHWEEKDATE

    (

       D DATE DEFAULT SYSDATE,

       W INTEGER DEFAULT 1

    ) RETURN DATE;

    FUNCTION YMCHAR(D VARCHAR2) RETURN VARCHAR2;

    FUNCTION YMCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION YMDCHAR(D VARCHAR2) RETURN VARCHAR2;

    FUNCTION YMDCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION MONTHEX(DATESTRING VARCHAR2) RETURN VARCHAR2;

    FUNCTION MONTHEX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION YEAREX(DATESTRING VARCHAR2) RETURN VARCHAR2;

    FUNCTION YEAREX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION DAYEX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2;

    FUNCTION DAYEX(DATESTRING VARCHAR2) RETURN VARCHAR2;

    FUNCTION TODATECHAR(D VARCHAR2) RETURN VARCHAR2;

    FUNCTION SEASON(M INTEGER) RETURN INTEGER;

END PKGDATE;

/

 

prompt

prompt Creating package body PKGDATE

prompt =============================

prompt

CREATE OR REPLACE PACKAGE BODY PKGDATE AS

    FUNCTION NOW RETURN DATE AS

    BEGIN

       RETURN GVNOW;

    END;

    FUNCTION TODAY RETURN DATE AS

    BEGIN

       RETURN GVTODAY;

    END;

    FUNCTION YEAR(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN TO_CHAR(ToDate(DATESTRING), 'YYYY');

    END;

    FUNCTION YEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_CHAR(D, 'YYYY');

    END;

    FUNCTION SEASON(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN SEASON(TODATETIME(DATESTRING));

    END;

    FUNCTION SEASON(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

       M INTEGER;

    BEGIN

       M := MONTH(D);

       RETURN CEIL(M / 3);

    END;

    FUNCTION MONTH(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(TODATE(DATESTRING), 'MM'));

    END;

    FUNCTION MONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'MM'));

    END;

    FUNCTION DAY(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

        RETURN TO_NUMBER(TO_CHAR(TODATE(DATESTRING), 'DD'));

    END;

    FUNCTION DAY(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'DD'));

    END;

    FUNCTION DAYOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN DAYOFYEAR(TODATE(DATESTRING));

    END;

    FUNCTION DAYOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'DDD'));

    END;

    FUNCTION HOUR(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(TODATE(DATESTRING), 'HH'));

    END;

    FUNCTION HOUR(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'HH'));

    END;

    FUNCTION MINUTE(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(TODATE(DATESTRING), 'MI'));

    END;

    FUNCTION MINUTE(D DATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'MI'));

    END;

    FUNCTION SECOND(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(TODATE(DATESTRING), 'SS'));

    END;

    FUNCTION SECOND(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'SS'));

    END;

    FUNCTION WEEK(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'D'));

    END;

    FUNCTION WEEKEX(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

       RESULT INTEGER;

    BEGIN

       RESULT := WEEK(D);

       IF RESULT = 1 THEN

           RESULT := 7;

       ELSE

           RESULT := RESULT - 1;

       END IF;

       RETURN(RESULT);

    END;

    FUNCTION CNWEEK(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN SUBSTR(CSTCNWEEKS, TO_NUMBER(TO_CHAR(D, 'D')), 1);

    END;

    FUNCTION WEEKOFMONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'W'));

    END;

    FUNCTION WEEKOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN TO_NUMBER(TO_CHAR(D, 'WW'));

    END;

    FUNCTION TODATE

    (

       Y INTEGER,

       M INTEGER DEFAULT 1,

       D INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(Y || '-' || M || '-' || D, CSTDATEFMT);

    END TODATE;

    FUNCTION TODATE(YMD VARCHAR2) RETURN DATE AS

       STR  VARCHAR2(20);

       IDX  INTEGER;

       IDX2 INTEGER;

    BEGIN

        STR  := TRIM(YMD);

       IDX  := INSTR(STR, ' ');

       IDX2 := INSTR(STR, '-');

       IF IDX > 0 THEN

           STR := TRIM(SUBSTR(STR, 1, IDX));

       END IF;

       IF LENGTH(STR) = 8

            AND IDX2 < 1 THEN

          STR := SUBSTR(STR, 1, 4) || CSTDATESPLIT || SUBSTR(STR, 5, 2) || CSTDATESPLIT || SUBSTR(STR, 7, 2);

       END IF;

       RETURN TO_DATE(STR, CSTDATEFMT);

    END TODATE;

    FUNCTION TODATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(TO_CHAR(D, CSTDATEFMT), CSTDATEFMT);

    END TODATE;

    FUNCTION TODATETIME

    (

       Y INTEGER,

       M INTEGER,

       D INTEGER,

       H INTEGER,

       I INTEGER,

       S INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(Y || CSTDATESPLIT || M || CSTDATESPLIT || D || CSTSPACE || H || CSTTIMESPLIT || I || CSTTIMESPLIT || S, CSTDATETIMEFMT);

    END TODATETIME;

    FUNCTION TODATETIME(YMDHMS VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(YMDHMS, CSTDATETIMEFMT);

    END TODATETIME;

    FUNCTION TODATETIME(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(TO_CHAR(D, CSTDATETIMEFMT), CSTDATETIMEFMT);

    END TODATETIME;

    FUNCTION TOTIME

    (

       H INTEGER,

       I INTEGER,

       S INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(H || CSTTIMESPLIT || I || CSTTIMESPLIT || S, CSTTIMEFMT);

    END;

    FUNCTION TOTIME(HMS VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(HMS, CSTTIMEFMT);

    END;

    FUNCTION TOTIME(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TO_DATE(TO_CHAR(D, CSTTIMEFMT), CSTTIMEFMT);

    END;

    FUNCTION TOCHAR(DATESTRING VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TOCHAR(TODATETIME(DATESTRING));

    END;

    FUNCTION TOCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       IF HOUR(D) + MINUTE(D) + SECOND(D) = 0 THEN

           RETURN TODATECHAR(D);

       ELSE

           IF SECOND(D) = 0 THEN

              RETURN TODATECHAR(D) || ' ' || HOUR(D) || ':' || MINUTE(D);

           ELSE

              RETURN TODATETIMECHAR(D);

           END IF;

       END IF;

    END;

    FUNCTION TODATECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, CSTDATEFMT);

    END;

    FUNCTION TODATETIMECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, CSTDATETIMEFMT);

    END;

    FUNCTION TOTIMECHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, CSTTIMEFMT);

    END;

    FUNCTION TODATESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN 'TO_DATE(''' || TODATECHAR(D) || ''',''' || CSTDATEFMT || ''')';

    END;

    FUNCTION TODATETIMESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN 'TO_DATE(''' || TODATETIMECHAR(D) || ''',''' || CSTDATETIMEFMT || ''')';

    END;

    FUNCTION TOTIMESQL(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN 'TO_DATE(''' || TOTIMECHAR(D) || ''',''' || CSTTIMEFMT || ''')';

    END;

    FUNCTION YEARFIRSTDATE(Y INTEGER) RETURN DATE AS

    BEGIN

       RETURN TODATE(Y, 1, 1);

    END;

    FUNCTION YEARFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN YEARFIRSTDATE(TODATE(DATESTRING));

    END;

    FUNCTION YEARFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TRUNC(TODATE(D), 'YEAR');

    END;

    FUNCTION SEASONFIRSTDATE

    (

       Y INTEGER,

       S INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN SEASONFIRSTDATE(TODATE(Y, S, 1));

    END;

    FUNCTION SEASONFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN SEASONFIRSTDATE(TODATE(DATESTRING));

    END;

    FUNCTION SEASONFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

       Y      INTEGER;

       S      INTEGER;

       M      INTEGER;

       RESULT DATE;

    BEGIN

       Y      := YEAR(D);

       S      := SEASON(D);

       M      := (S - 1) * 3 + 1;

       RESULT := TODATE(Y, M, 1);

       RETURN(RESULT);

    END;

    FUNCTION MONTHFIRSTDATE

    (

       Y INTEGER,

       M INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN TODATE(Y, M, 1);

    END;

    FUNCTION MONTHFIRSTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN MONTHFIRSTDATE(TODATE(DATESTRING));

    END;

    FUNCTION MONTHFIRSTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TRUNC(D, 'MONTH');

    END;

    FUNCTION DATESTARTTIME(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN TODATETIME(TODATE(DATESTRING));

    END;

    FUNCTION DATESTARTTIME(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TODATETIME(TODATE(D));

    END;

    FUNCTION YEARLASTDATE(Y INTEGER) RETURN DATE AS

    BEGIN

       RETURN YEARLASTDATE(TODATE(Y, 1, 1));

    END;

    FUNCTION YEARLASTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN YEARLASTDATE(TODATE(DATESTRING));

    END;

    FUNCTION YEARLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN YEARFIRSTDATE(ADD_MONTHS(D, 12)) - CSTSecondsOfOneDay;

    END;

    FUNCTION SEASONLASTDATE

    (

       S INTEGER,

       Y INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN SEASONLASTDATE(TODATE(Y, S, 1));

    END;

    FUNCTION SEASONLASTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN SEASONLASTDATE(TODATE(DATESTRING));

    END;

    FUNCTION SEASONLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

       Y      INTEGER;

       S      INTEGER;

       M      INTEGER;

       RESULT DATE;

    BEGIN

       Y      := YEAR(D);

       S      := SEASON(D);

       M      := S * 3;

       RESULT := MONTHLASTDATE(Y, M);

       RETURN(RESULT);

    END;

    FUNCTION MONTHLASTDATE

    (

       Y INTEGER,

       M INTEGER

    ) RETURN DATE AS

    BEGIN

       RETURN MONTHLASTDATE(TODATE(Y, M, 1));

    END;

    FUNCTION MONTHLASTDATE(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN MONTHFIRSTDATE(TODATE(DATESTRING));

    END;

    FUNCTION MONTHLASTDATE(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN MONTHFIRSTDATE(ADD_MONTHS(D, 1)) - CSTSecondsOfOneDay;

    END;

    FUNCTION DATEENDTIME(DATESTRING VARCHAR2) RETURN DATE AS

    BEGIN

       RETURN DATEENDTIME(TODATE(DATESTRING));

    END;

    FUNCTION DATEENDTIME(D DATE DEFAULT SYSDATE) RETURN DATE AS

    BEGIN

       RETURN TODATETIME(TODATE(D) + 1) - CSTSecondsOfOneDay;

    END;

    FUNCTION DAYSBETWEEN(D1 DATE) RETURN INTEGER AS

    BEGIN

       RETURN DAYSBETWEEN(D1, NOW);

    END;

    FUNCTION DAYSBETWEEN(D1 VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN DAYSBETWEEN(TODATE(D1), NOW);

    END;

    FUNCTION DAYSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER AS

    BEGIN

       RETURN DAYSBETWEEN(TODATE(D1), TODATE(D2));

    END;

    FUNCTION DAYSBETWEEN

    (

       D1 DATE,

       D2 DATE

    ) RETURN INTEGER AS

       RESULT INTEGER;

    BEGIN

       RESULT := TODATE(D1) - TODATE(D2);

       RETURN(RESULT);

    END;

    FUNCTION WEEKSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER AS

    BEGIN

       RETURN WEEKSBETWEEN(TODATE(D1), TODATE(D2));

    END;

    FUNCTION WEEKSBETWEEN

    (

       D1 DATE DEFAULT SYSDATE,

       D2 DATE DEFAULT SYSDATE

    ) RETURN INTEGER AS

       RESULT INTEGER;

    BEGIN

       RESULT := FLOOR((TODATE(D1) - TODATE(D2)) / 7);

       /*IF RESULT >= 7 THEN

      RESULT := FLOOR(RESULT / 7);

    ELSE

      IF YEAR(D1) = YEAR(D2) THEN

        RESULT := WEEKOFYEAR(D1) - WEEKOFYEAR(D2);

      ELSE*/

       IF WEEK(D1 - 7 * RESULT) < WEEK(D2) THEN

           RESULT := RESULT + 1;

       END IF;

       --END IF;

       --END IF;

       RETURN(RESULT);

    END;

    FUNCTION MONTHSBETWEEN(D1 DATE) RETURN INTEGER AS

    BEGIN

       RETURN MONTHSBETWEEN(D1, NOW);

    END;

    FUNCTION MONTHSBETWEEN(D1 VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN MONTHSBETWEEN(TODATE(D1), NOW);

    END;

    FUNCTION MONTHSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER AS

    BEGIN

       RETURN MONTHSBETWEEN(TODATE(D1), TODATE(D2));

    END;

    FUNCTION MONTHSBETWEEN

    (

       D1 DATE,

       D2 DATE

    ) RETURN INTEGER AS

    BEGIN

       RETURN YEARSBETWEEN(D2, D1) * 12 + MONTH(D1) - MONTH(D2);

    END;

    FUNCTION YEARSBETWEEN

    (

       D1 VARCHAR2,

       D2 VARCHAR2

    ) RETURN INTEGER AS

    BEGIN

       RETURN YEARSBETWEEN(TODATE(D1), TODATE(D2));

    END;

    FUNCTION YEARSBETWEEN

    (

       D1 DATE DEFAULT SYSDATE,

       D2 DATE DEFAULT SYSDATE

    ) RETURN INTEGER AS

    BEGIN

       RETURN YEAR(D1) - YEAR(D2);

    END;

    FUNCTION DAYSOFYEAR(Y INTEGER) RETURN INTEGER AS

    BEGIN

       RETURN DAYSOFYEAR(YEARFIRSTDATE(Y));

    END;

    FUNCTION DAYSOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN DAYSOFYEAR(TODATETIME(DATESTRING));

    END;

    FUNCTION DAYSOFYEAR(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN ADD_MONTHS(TRUNC(D, 'YEAR'), 12) - TRUNC(D, 'YEAR');

    END;

    FUNCTION DAYSOFMONTH

    (

       Y INTEGER,

       M INTEGER

    ) RETURN INTEGER AS

    BEGIN

       RETURN DAYSOFMONTH(MONTHFIRSTDATE(Y, M));

    END;

    FUNCTION DAYSOFMONTH(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN DAYSOFMONTH(TODATETIME(DATESTRING));

    END;

    FUNCTION DAYSOFMONTH(D DATE DEFAULT SYSDATE) RETURN INTEGER AS

    BEGIN

       RETURN ADD_MONTHS(TRUNC(D, 'MONTH'), 1) - TRUNC(D, 'MONTH');

    END;

    FUNCTION MONTHDATECOLLECTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN TCDATE AS

       RESULT TCDATE := TCDATE();

    BEGIN

       FOR I IN 1 .. DAYSOFMONTH(Y, M) LOOP

           RESULT.EXTEND(1);

           RESULT(RESULT.COUNT) := TODATE(Y, M, I);

       END LOOP ;

       RETURN(RESULT);

    END;

    FUNCTION MONTHDATECOLLECTION(D DATE) RETURN TCDATE AS

    BEGIN

       RETURN(MONTHDATECOLLECTION(PKGDATE.YEAR(D), PKGDATE.MONTH(D)));

    END;

    FUNCTION MONTHDATECAPTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN VARCHAR2 AS

       VDATE  DATE;

       RESULT VARCHAR2(4000);

    BEGIN

       VDATE := MONTHFIRSTDATE(Y, M);

       FOR I IN 1 .. DAYSOFMONTH(Y, M) LOOP

           IF I = 1 THEN

              RESULT := '"' || TOCHAR(VDATE) || '"';

           ELSE

              RESULT := RESULT || ',"' || TOCHAR(VDATE + I - 1) || '"';

           END IF;

       END LOOP ;

       RETURN(RESULT);

    END;

    FUNCTION MONTHDAYCAPTION

    (

       Y INTEGER,

       M INTEGER

    ) RETURN VARCHAR2 AS

       VDATE  DATE;

       VDAY   CHAR(2);

       RESULT VARCHAR2(4000);

    BEGIN

       VDATE := MONTHFIRSTDATE(Y, M);

       FOR I IN 1 .. DAYSOFMONTH(Y, M) LOOP

           IF I = 1 THEN

              VDAY   := DAY(VDATE);

              RESULT := '"' || TO_CHAR(TO_NUMBER(VDAY)) || '"';

           ELSE

              VDAY   := DAY(VDATE + I - 1);

              RESULT := RESULT || ',"' || TO_CHAR(TO_NUMBER(VDAY)) || '"';

              --        RESULT := RESULT || ',"' || TOCHAR(VDATE + I - 1) || '"';

           END IF;

       END LOOP ;

       RETURN(RESULT);

    END;

    FUNCTION WEEKOFYEAR(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN WEEKOFYEAR(TODATE(DATESTRING));

    END;

    FUNCTION WEEKOFMONTH(DATESTRING VARCHAR2) RETURN INTEGER AS

    BEGIN

       RETURN WEEKOFMONTH(TODATE(DATESTRING));

    END;

    FUNCTION YEARWEEKDATE

    (

       Y INTEGER,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN YEARFIRSTDATE +(W - 1) * 7;

    END;

    FUNCTION YEARWEEKDATE

    (

       D VARCHAR2,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN YEARWEEKDATE(PKGDATE.YEAR(D), W);

    END;

    FUNCTION YEARWEEKDATE

    (

       D DATE DEFAULT SYSDATE,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN YEARWEEKDATE(PKGDATE.YEAR(D), W);

    END;

    FUNCTION MONTHWEEKDATE

    (

       Y INTEGER,

       M INTEGER,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN MONTHFIRSTDATE(Y, M) +(W - 1) * 7;

    END;

    FUNCTION MONTHWEEKDATE

    (

       D VARCHAR2,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN MONTHWEEKDATE(PKGDATE.YEAR(D), PKGDATE.MONTH(D), W);

    END;

    FUNCTION MONTHWEEKDATE

    (

       D DATE DEFAULT SYSDATE,

       W INTEGER DEFAULT 1

    ) RETURN DATE AS

    BEGIN

       RETURN MONTHWEEKDATE(PKGDATE.YEAR(D), PKGDATE.MONTH(D), W);

    END;

    FUNCTION YMCHAR(D VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(D), 'yyyymmdd');

    END;

    FUNCTION YMCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, 'yyyymm');

    END;

    FUNCTION YMDCHAR(D VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(D), 'yyyymmdd');

    END;

    FUNCTION YMDCHAR(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, 'yyyymmdd');

    END;

    FUNCTION MONTHEX(DATESTRING VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(DATESTRING), 'MM');

    END;

    FUNCTION MONTHEX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, 'MM');

    END;

    FUNCTION YEAREX(DATESTRING VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(DATESTRING), 'YY');

    END;

    FUNCTION YEAREX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, 'YY');

    END;

    FUNCTION DAYEX(D DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(D, 'DD');

    END;

    FUNCTION DAYEX(DATESTRING VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(DATESTRING), 'DD');

    END;

    FUNCTION TODATECHAR(D VARCHAR2) RETURN VARCHAR2 AS

    BEGIN

       RETURN TO_CHAR(TODATE(D), CSTDATEFMT);

    END;

    FUNCTION SEASON(M INTEGER) RETURN INTEGER AS

    BEGIN

       RETURN CEIL(M / 3);

    END;

END PKGDATE;

/

 

 

spool off