ETL,
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.
