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 )

The cool thing about this script is you don’t need a fixed date table and can adjust as needed.

OracleDateTable

One Comment

  1. Great article !

Leave a Comment

Your email address will not be published. Required fields are marked *