Date dimension script with Oracle SQL
The script below will create a date dimension in just one SQL statement. We don’t use performance-killer nonsense such as cursors, functions etc.
We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 lines of code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> CREATE TABLE d_date AS 2 SELECT 3 n AS Date_ID, 4 TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date, 5 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days, 6 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short, 7 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num, 8 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long, 9 TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year 10 FROM ( 11 select level n 12 from dual 13 connect by level <= 2000 14 ); Table created. |
Pretty much self-explanatory. The above will create a date dimension starting at 01/01/2008 and ending at 01/01/2008+2000 days = 22/06/2013.
If you want to read up on NUMTODSINTERVAL have a look at the documentation. Intervals are also very well explained in Tom Kyte’s Expert Oracle Database Architecture. It is worth buying just for this chapter.
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
