Returning the Days in a Month

I first wrote a version of this article in June 2000 after someone posted the following question in an Oracle newsgroup:

I'm trying to create a SQL statement which will give me all the days of a given month as individual rows.

This struck me as an interesting question. SQL has the built-in function LAST_DAY that returns the date of the last day in any given month. You can work from that to find out whether the month in question contains 31, 30, 28, or 29 days. For example:

SELECT SYSDATE, TO_CHAR(LAST_DAY(SYSDATE), 'DD')
FROM dual;

SYSDATE   TO
--------- --
14-NOV-10 30

Once you know the number of days in the month, all that remains is to query against a table having at least that many rows. My original article proposed two solutions yielding correct results, but I now replace those with the following, more elegant solution from Anton Zlygostev:

SELECT LEVEL,
       TO_DATE(TO_CHAR(LEVEL, '09')||
               TO_CHAR(EXTRACT(MONTH FROM SYSDATE), '09')||
               TO_CHAR(EXTRACT(YEAR FROM SYSDATE), '9999'), 
       'dd.mm.yyyy') mon_day
FROM dual
WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE))
CONNECT BY LEVEL=ROWNUM;

     LEVEL MON_DAY
---------- ---------
         1 01-NOV-10
         2 02-NOV-10
         3 03-NOV-10
         ...
        28 03-NOV-10
        29 02-NOV-10
        30 01-NOV-10

At the core of Anton's solution is the elegant use of a CONNECT BY query against the table DUAL as a row-generator. I want to thank him for sharing that solution. It's much better than either of the two that I came up with ten years ago.

Note! See Dates in A Range for a recent follow-up in which I generalize the problem across monthly boundaries.

Riffing on Anton's solution, I came up with a variation using date arithmetic rather than conversion to and from character strings:

SELECT LEVEL, LAST_DAY(SYSDATE)-LEVEL+1, 
       LAST_DAY(ADD_MONTHS(SYSDATE, -1))+LEVEL
FROM dual
WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE))
CONNECT BY LEVEL=ROWNUM;

     LEVEL LAST_DAY( LAST_DAY(
---------- --------- ---------
         1 30-NOV-10 01-NOV-10
         2 29-NOV-10 02-NOV-10
         3 28-NOV-10 03-NOV-10
         ...
        28 03-NOV-10 28-NOV-10
        29 02-NOV-10 29-NOV-10
        30 01-NOV-10 30-NOV-10

Execute the expression LAST_DAY(SYSDATE)-LEVEL+1 to return the days of the month in descending order. If you must have the days in ascending order, then execute the second, more complex expression. Choose the simpler expression if your final query will include an ORDER BY clause. If you plan to sort your results anyway, you might as well save on CPU by keeping to the simpler expression.

Which approach is more efficient? My testing showed a slight advantage in CPU time for the date-arithmetic approaches. Following is a SQL*Plus script containing a PL/SQL block that I wrote to exercise each approach 10,000 times. I executed the block once with tracing disabled, and again with tracing enabled.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
DECLARE
CURSOR var1 IS
SELECT LEVEL,
       TO_DATE(TO_CHAR(LEVEL, '09')||
               TO_CHAR(EXTRACT(MONTH FROM SYSDATE), '09')||
               TO_CHAR(EXTRACT(YEAR FROM SYSDATE), '9999'), 
       'dd.mm.yyyy') mon_day
FROM dual
WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE))
CONNECT BY LEVEL=ROWNUM;

CURSOR var2 IS
SELECT LEVEL, LAST_DAY(SYSDATE)-LEVEL+1
FROM dual
WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE))
CONNECT BY LEVEL=ROWNUM;

CURSOR var3 IS
SELECT LEVEL, LAST_DAY(ADD_MONTHS(SYSDATE, -1))+LEVEL
FROM dual
WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE))
CONNECT BY LEVEL=ROWNUM;

BEGIN
  FOR a IN 1..10000 LOOP
    FOR x IN var1 LOOP
      NULL;
    END LOOP;
    FOR y IN var2 LOOP
      NULL;
    END LOOP;
    FOR z IN var3 LOOP
      NULL;
    END LOOP;
  END LOOP;
END;
/
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Over the space of 10,000 executions on the PC that I happen to be using, there was a slight difference in favor of the date-arithmetic approach. Here are the relevant sections from the TKPROF output:

SQL ID: 6x7n32sy9dwaw
Plan Hash: 634278704
SELECT LEVEL, TO_DATE(TO_CHAR(LEVEL, '09')|| TO_CHAR(EXTRACT(MONTH 
FROM
 SYSDATE), '09')|| TO_CHAR(EXTRACT(YEAR FROM SYSDATE), '9999'), 'dd.mm.yyyy') 
  MON_DAY FROM DUAL WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE)) 
  CONNECT BY LEVEL=ROWNUM


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.21       0.22          0          0          0           0
Fetch    10000      2.15       2.23          0          0          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      2.37       2.46          0          0          0      300000
SQL ID: 29mx64kxr8rua
Plan Hash: 634278704
SELECT LEVEL, LAST_DAY(SYSDATE)-LEVEL+1 
FROM
 DUAL WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE)) CONNECT BY LEVEL=
  ROWNUM


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.14       0.24          0          0          0           0
Fetch    10000      1.54       1.68          0          0          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      1.68       1.92          0          0          0      300000
SQL ID: g0nqxhwkf39py
Plan Hash: 634278704
SELECT LEVEL, LAST_DAY(ADD_MONTHS(SYSDATE, -1))+LEVEL 
FROM
 DUAL WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(SYSDATE)) CONNECT BY LEVEL=
  ROWNUM


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.21       0.22          0          0          0           0
Fetch    10000      1.62       1.59          0          0          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      1.84       1.82          0          0          0      300000

Look at the CPU times for the first and third queries, which each return their results in ascending order by date. You'll see a 2.37 - 1.84 = 0.53 second advantage in favor of the query using date arithmetic. The second query uses a simpler expression to return results in descending order, and is marginally faster yet.

I prefer the readability of the date-arithmetic approach, so I'm happy to see a slight advantage in favor if it. However, that advantage seems academic unless you are hammering the query by executing it tens of thousands of times per minute. If you're writing a report that runs just a few times per day, then the three approaches in this article are for all practical purposes identical.

Thanks again to Anton Zlygostev for sharing his solution to the problem of returning a row for each day in the current month. His CONNECT BY row-generator approach is more elegant than either of my original solutions. I've enjoyed revisiting this article.