Create a Date Table in Oracle

Occasionally you may be working with non Microsoft data sources such as Oracle over front end tools like Power Pivot or Reporting Services.

Some options for a date table include creating a linked table in Power Pivot or generating a table in the data source. Generate a date table quickly using your Oracle data source with the following example which generates a virtual date table, one row from the sample date of 11th April 2013 until 720 days after…

SELECT
       TO_NUMBER (TO_CHAR (TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'), 'yyyymmdd')) AS date_key,
n AS Date_ID,
TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
FROM (
select level n
from dual
connect by level <= 720
);

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s