16 Aug 2017

Generating Calendar

--GENERATING DATES FOR ONE YEAR
SELECT TRUNC(SYSDATE,'YEAR') + LEVEL-1 AS GEN_DATE
FROM DUAL
CONNECT BY TRUNC(SYSDATE,'YEAR') +LEVEL-1 < ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)

OUTPUT
    GEN_DATE
1 1/1/2017
2 1/2/2017
3 1/3/2017
4 1/4/2017
5 1/5/2017
...
361 12/27/2017
362 12/28/2017
363 12/29/2017
364 12/30/2017
365 12/31/2017

Generating The Transaction Dates

--GENERATE_DATES
WITH
START_AND_END(START_DATE,END_DATE)
AS (SELECT TRUNC(SYSDATE,'MONTH'), LAST_DAY(SYSDATE) FROM DUAL)
SELECT START_DATE + LEVEL-1 AS TRANS_DATE
FROM START_AND_END
CONNECT BY LEVEL <=END_DATE - START_DATE +1;

Output
    TRANS_DATE
1 8/1/2017
2 8/2/2017
3 8/3/2017
4 8/4/2017
5 8/5/2017
6 8/6/2017
7 8/7/2017
8 8/8/2017
9 8/9/2017
10 8/10/2017
11 8/11/2017
12 8/12/2017
13 8/13/2017
14 8/14/2017
15 8/15/2017
16 8/16/2017
17 8/17/2017
18 8/18/2017
19 8/19/2017
20 8/20/2017
21 8/21/2017
22 8/22/2017
23 8/23/2017
24 8/24/2017
25 8/25/2017
26 8/26/2017
27 8/27/2017
28 8/28/2017
29 8/29/2017
30 8/30/2017
31 8/31/2017