Date dimension script with Oracle SQL

Published on February 24, 2009
Updated on June 28, 2021

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.

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.