This statement will create basic Date Dimension table with data starting from 1-Jan-2014 and till plus 2000 days.
CREATE TABLE DATE_DIM AS
SELECT
n AS Date_ID, -- (This is just a serial number not a date)
1 as DATE_KEY,
TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date, --- (PK)
TO_CHAR(TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
TO_CHAR(TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
TO_CHAR(TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
TO_CHAR(TO_DATE('31/12/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS YEAR
FROM (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL <= 2000
);
update DATE_DIM SET DATE_KEY = YEAR||Month_Num||Days ;
--DATE_KEY in in format YYYYMMDD
