Date dimension script with Oracle SQL

Uli Bethke ETL, Oracle, SQL for Analysis

The script below will create a date dimension in just one SQL statement. We don't use performance-killer nonsense such as cursors, functions etc.

Teach me Big Data to Advance my Career

We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 lines of code.

Pretty much self-explanatory. The above will create a date dimension starting at 01/01/2008 and ending at 01/01/2008+2000 days = 22/06/2013.

If you want to read up on NUMTODSINTERVAL have a look at the documentation. Intervals are also very well explained in Tom Kyte's Expert Oracle Database Architecture. It is worth buying just for this chapter.