Oracle Database - How to create a time dimension table in Sql ?

> Database > Oracle Database

1 - About

This statement create a simple time dimension without any cursor only in native oracle sql.

3 - Code

SQL> CREATE TABLE d_date AS
  2  SELECT
  3     n AS Date_ID,
  4     TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
  5     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
  6     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
  7     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
  8     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
  9     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS YEAR
 10  FROM (
 11  SELECT LEVEL n
 12  FROM dual
 13  CONNECT BY LEVEL <= 2000
 14  );
 
TABLE created.

4 - Reference

Advertising