Date dimension in Oracle with one SQL statement

View previous topic View next topic Go down

Date dimension in Oracle with one SQL statement

Post  ubethke on Thu Feb 26, 2009 1:54 pm

CREATE TABLE d_date AS
SELECT
n AS Date_ID,
TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
FROM (
select level n
from dual
connect by level <= 2000
);

Have a look at my blog for some explanation: http://www.business-intelligence-quotient.com/?p=84

ubethke

Posts : 28
Join date : 2009-02-03

View user profile http://www.business-intelligence-quotient.com

Back to top Go down

Re: Date dimension in Oracle with one SQL statement

Post  ngalemmo on Tue May 19, 2009 2:52 pm

Nice trick... using DUAL to generate rows.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum