### Monthly calendar

S

Simple calendar for whole year usable month by month.

```SELECT
TO_NUMBER(SUBSTR(x.week, 3))                                                AS week,
x.month,
MAX(DECODE(TO_CHAR(x.day, 'D'), '1', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS mon,
MAX(DECODE(TO_CHAR(x.day, 'D'), '2', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS tue,
MAX(DECODE(TO_CHAR(x.day, 'D'), '3', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS wed,
MAX(DECODE(TO_CHAR(x.day, 'D'), '4', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS thu,
MAX(DECODE(TO_CHAR(x.day, 'D'), '5', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS fri,
MAX(DECODE(TO_CHAR(x.day, 'D'), '6', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS sat,
MAX(DECODE(TO_CHAR(x.day, 'D'), '7', TO_NUMBER(TO_CHAR(x.day, 'FMDD'))))    AS sun
FROM (
SELECT
TRUNC(SYSDATE, 'Y') + LEVEL - 1                                         AS day,
TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'IYIW')                        AS week,
TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY')                     AS month
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y')
) x
GROUP BY x.month, x.week
ORDER BY x.month, x.week;
```

You can calculate next day and also next business day (if you apply function above):

```DECLARE
in_date   CONSTANT DATE := TRUNC(SYSDATE);
--
FUNCTION next_date (
in_day    CHAR,
in_date   DATE := TRUNC(SYSDATE)
)
RETURN DATE AS
BEGIN
RETURN CASE in_day
WHEN 'D' THEN
in_date + 1
WHEN 'W' THEN
NEXT_DAY(in_date, TO_CHAR(TRUNC(in_date, 'D'), 'DY'))
WHEN 'M' THEN
LAST_DAY(in_date) + 1
WHEN 'Q' THEN
CASE WHEN EXTRACT(MONTH FROM in_date) MOD 3 = 0 THEN
ELSE
TRUNC(ADD_MONTHS(in_date, (3 - (EXTRACT(MONTH FROM in_date) MOD 3)) + 1), 'MM')
END
WHEN 'Y' THEN
TO_DATE((EXTRACT(YEAR FROM in_date) + 1) || '-01-01', 'YYYY-MM-DD')
END;
END;
BEGIN
-- show next day, week (Monday), quarter, year and number of days left
FOR c IN (
SELECT 'D' AS day_ FROM DUAL UNION ALL
SELECT 'W' FROM DUAL UNION ALL
SELECT 'M' FROM DUAL UNION ALL
SELECT 'Q' FROM DUAL UNION ALL
SELECT 'Y' FROM DUAL
) LOOP
DBMS_OUTPUT.PUT_LINE(c.day_ || ' ' || TO_CHAR(next_date(c.day_, in_date), 'YYYY-MM-DD') || ' ' || LPAD(next_date(c.day_, in_date) - TRUNC(SYSDATE), 4));
END LOOP;
END;
/
```

On a related note, you can set default NLS parameters for your session.

```BEGIN
DBMS_SESSION.SET_NLS('NLS_LANGUAGE',            '''CZECH''');
DBMS_SESSION.SET_NLS('NLS_TERRITORY',           '''CZECH REPUBLIC''');
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT',         '''DD.MM.RR''');
DBMS_SESSION.SET_NLS('NLS_TIME_FORMAT',         '''HH24:MI:SSXFF''');
DBMS_SESSION.SET_NLS('NLS_TIMESTAMP_FORMAT',    '''DD.MM.RR HH24:MI:SSXFF''');
END;
/
```