Let’s create a date dimension with pure Oracle SQL
Posted: February 24th, 2009 | Author: Uli Bethke | Filed under: ETL, Oracle, SQL for Analysis | Tags: Date dimension, Oracle, sql script | 2 Comments »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.
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.
I simplified the SQL and added week day and quarter dimensions.
CREATE TABLE d_date AS
SELECT
n AS Date_ID,
CurrDate AS Full_Date,
TO_CHAR(CurrDate,’DD’) AS Days,
TO_CHAR(CurrDate,’Mon’) AS Month_Short,
TO_CHAR(CurrDate,’MM’) AS Month_Num,
TO_CHAR(CurrDate,’Month’) AS Month_Long,
TO_CHAR(CurrDate,’YYYY’) AS Year,
TO_CHAR(CurrDate,’Day’) as Week_Day,
TO_CHAR(CurrDate,’Q') AS Quarter
FROM (
select level n, TO_DATE(’31/12/2007′,’DD/MM/YYYY’) + NUMTODSINTERVAL(level,’day’) CurrDate
from dual
connect by level <= 2000)
;
Uli Bethke,
Excellent SQL.. very usefull..