Oracle SQL Query to Print all dates with day from a give Date interval.

SQL Query

SELECT lvl_date "Date",

       TO_CHAR(lvl_date, 'Day') "Day 1",

       TO_CHAR(lvl_date, 'Dy') "Day 2"

  FROM (SELECT (LEVEL + TO_DATE('01-JAN-2020')) - 1 lvl_date       

          FROM dual

         CONNECT BY LEVEL <= (TO_DATE('31-JAN-2020') - TO_DATE('01-JAN-2020')) + 1)

Output

Date            Day 1           Day 2                   

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

01/01/2020 Wednesday Wed

02/01/2020 Thursday Thu

03/01/2020 Friday    Fri

04/01/2020 Saturday Sat

05/01/2020 Sunday    Sun

06/01/2020 Monday    Mon

07/01/2020 Tuesday  Tue

08/01/2020 Wednesday Wed

09/01/2020 Thursday Thu

10/01/2020 Friday    Fri

11/01/2020 Saturday Sat

12/01/2020 Sunday    Sun

13/01/2020 Monday    Mon

14/01/2020 Tuesday  Tue

15/01/2020 Wednesday Wed

16/01/2020 Thursday Thu

17/01/2020 Friday    Fri

18/01/2020 Saturday Sat

19/01/2020 Sunday    Sun

20/01/2020 Monday    Mon

21/01/2020 Tuesday  Tue

22/01/2020 Wednesday Wed

23/01/2020 Thursday Thu

24/01/2020 Friday    Fri

25/01/2020 Saturday Sat

26/01/2020 Sunday    Sun

27/01/2020 Monday    Mon

28/01/2020 Tuesday  Tue

29/01/2020 Wednesday Wed

30/01/2020 Thursday Thu

31/01/2020 Friday    Fri