Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Date dimension in Oracle with one SQL statement

Go down

Date dimension in Oracle with one SQL statement Empty Date dimension in Oracle with one SQL statement

Post  ubethke 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

http://www.business-intelligence-quotient.com

Back to top Go down

Date dimension in Oracle with one SQL statement Empty Re: Date dimension in Oracle with one SQL statement

Post  ngalemmo Tue May 19, 2009 2:52 pm

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

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

http://aginity.com

Back to top Go down

Back to top


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