Returning the Days in a Month
Recently, in one of the Oracle newsgroups, someone posted the following question:
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 built-in function named LAST_DAY that returns the date of the last day in any given month, and you can work from that to find out whether the month in question contains 31, 30, 28, or 29 days. The following example shows this being done for the date on which I wrote this article:
SQL> SELECT TO_CHAR(LAST_DAY(SYSDATE),'dd') 2 FROM dual; TO -- 30
This gets us to "30 days hath June" and so forth, but what the person posting the original question wanted was for the query to return one row for each day of the month. There is a maximum of 31 days in any given month, so you need a table with at least that many rows to work with. My solution was to create a table called MON, and insert 31 rows into it. Here's what I did:
SQL> CREATE TABLE mon ( 2 mon_day NUMBER 3 ); Table created. SQL> INSERT INTO mon VALUES (1); 1 row created. SQL> INSERT INTO mon VALUES (2); 1 row created. ... SQL> INSERT INTO mon (VALUES (31); 1 row created.
As you can see, the table MON contains one column named MON_DAY. The 31 rows in the table contained the values 1..31 in that column. With the MON table in place, you can write a query that returns one row for each day in the month. Restrict the query to the proper number of rows by using the expression shown earlier in this article that returns the number of days in a given month. For example:
SQL> SELECT mon_day, TO_CHAR(SYSDATE,'Mon') ||
2 '-' || LTRIM(TO_CHAR(mon_day))
3 || '-' || TO_CHAR(SYSDATE,'YYYY')
4 FROM mon
5 WHERE mon_day
6 <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'));
MON_DAY TO_CHAR(SYSDATE,'MON')||'-'||LTRIM(TO_CHAR(MON_DA
---------- -------------------------------------------------
1 Jun-1-2000
2 Jun-2-2000
3 Jun-3-2000
...
30 Jun-30-2000
This query returns the contents of the MON_DAY column, and also uses that value in an expression that returns the date associated with that day of the month. As you may have guessed, I wrote this article in June 2000. The results of this query can then be joined in with any other tables that you need to query, and in fact that's exactly what the person posting the question did upon receiving this solution.
Is there a solution that doesn't involve the creation of a table just to contain 31 rows corresponding to the 31 possible days in a month? It turns out that there is. Using a rather long, and I think ugly, inline view, you can return the same results without using a table such as MON. Here's how:
SQL> SELECT mon_day, TO_CHAR(SYSDATE,'Mon') ||
2 '-' || LTRIM(TO_CHAR(mon_day))
3 || '-' || TO_CHAR(SYSDATE,'YYYY')
4 FROM
5 (select 1 mon_day from dual
6 union select 2 mon_day from dual
7 union select 3 mon_day from dual
8 union select 4 mon_day from dual
9 union select 5 mon_day from dual
10 union select 6 mon_day from dual
11 union select 7 mon_day from dual
12 union select 8 mon_day from dual
13 union select 9 mon_day from dual
14 union select 10 mon_day from dual
15 union select 11 mon_day from dual
16 union select 12 mon_day from dual
17 union select 13 mon_day from dual
18 union select 14 mon_day from dual
19 union select 15 mon_day from dual
20 union select 16 mon_day from dual
21 union select 17 mon_day from dual
22 union select 18 mon_day from dual
23 union select 19 mon_day from dual
24 union select 20 mon_day from dual
25 union select 21 mon_day from dual
26 union select 22 mon_day from dual
27 union select 23 mon_day from dual
28 union select 24 mon_day from dual
29 union select 25 mon_day from dual
30 union select 26 mon_day from dual
31 union select 27 mon_day from dual
32 union select 28 mon_day from dual
33 union select 29 mon_day from dual
34 union select 30 mon_day from dual
35 union select 31 mon_day from dual)
36 WHERE mon_day
37 <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'));
MON_DAY TO_CHAR(SYSDATE,'MON')||'-'||LTRIM(TO_CHAR(MON_DA
---------- -------------------------------------------------
1 Jun-1-2000
2 Jun-2-2000
3 Jun-3-2000
...
30 Jun-30-2000
In this last example, the required 31 rows were created through an inline view that unioned together 31 SELECTs against the DUAL table. If you were going to use this solution, it would probably be best to encapsulate that union query into a view. Your SELECT statements would be shorter and more understandable, you wouldn't run the risk of forgetting one of the 31 queries in the union, and you could change the view, and thus your method for generating 31 rows, and have that change propogated to all your existing queries.