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);
понедельник, 3 февраля 2014 г.
oracle generate calendar
Подписаться на:
Комментарии к сообщению (Atom)
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;