понедельник, 3 февраля 2014 г.

oracle generate calendar 

SELECT TO_NUMBER (TO_CHAR (daterange, 'yyyy')) AS year,
       TO_NUMBER (TO_CHAR (daterange, 'yyyymmdd')) AS date_id,
       TRIM (TO_CHAR (daterange, 'month')) AS month,
       TO_NUMBER (TO_CHAR (daterange, 'mm')) AS month_id,
       TO_NUMBER (TO_CHAR (daterange, 'dd')) AS day_id,
       TRIM (TO_CHAR (daterange, 'day')) AS day,
       daterange AS calendar_date
  FROM (  SELECT DateRange
            FROM (    SELECT TO_DATE ('01.01.2005', 'dd.MM.YYYY') - 1 + LEVEL
                                AS DateRange
                        FROM DUAL
                       WHERE (TO_DATE ('01.01.2005', 'dd.MM.YYYY') - 1 + LEVEL) <=
                                LAST_DAY (
                                   ADD_MONTHS (
                                      SYSDATE,
                                      12 - TO_NUMBER (TO_CHAR (SYSDATE, 'mm'))))
                  CONNECT BY LEVEL <= 99999)
        ORDER BY DateRange);
Автор: на

1 комментарий:

  1. Николай Мишин24 февраля 2014 г. в 05:15

    declare
    CURSOR cur_tdbparams IS
    WITH t AS
    (SELECT TO_DATE('01.01.2013', 'dd.MM.YYYY') d1,
    TO_DATE('01.03.2013', 'dd.MM.YYYY') d2
    FROM DUAL),
    mon AS
    (SELECT DECODE(LEVEL, 1, d1, TRUNC(ADD_MONTHS(d1, LEVEL - 1), 'mm')) AS date_from,
    CASE
    WHEN ADD_MONTHS(TRUNC(d1, 'mm'), LEVEL) > d2 THEN
    d2
    ELSE
    LAST_DAY(ADD_MONTHS(d1, LEVEL - 1))
    END date_to
    FROM t
    CONNECT BY ADD_MONTHS(TRUNC(d1, 'mm'), LEVEL - 1) < d2)
    select date_to from mon;
    BEGIN
    FOR var_tdbparams IN cur_tdbparams LOOP
    DBMS_OUTPUT.put_line(to_char(var_tdbparams.date_to, 'dd.mm.yyyy'));
    END LOOP;
    END;

    ОтветитьУдалить

Подписаться на: Комментарии к сообщению (Atom)