A date dimension

Today I had to create a date dimension. This is a dimension that provides us with a list of records that contains the weekdays, the number of a month etc.
Let me give a screenshot of these records:

This table helps us to translate a date (like 08 – 25 – 2011) into a day in the week, like Monday, Tuesday. Such translation is by no means trivial. A table that contains the dates, along with, say, weekday names helps us in this task.

I encountered beautiful scripts to create such a table. I want to refer to http://www.dwhworld.com/2010/11/date-dimension-sql-scripts-oracle/ to see such a script. Similarly take a look at http://kimballgroup.forumotion.net/t52-date-dimension-in-oracle-with-one-sql-statement. Or http://oracleolap.blogspot.nl/2011/01/script-for-time-dimension-table.html. Or http://code.google.com/p/oraclenerd/source/browse/trunk/misc/sql/tables/times.tab . Only one problem: only Oracle scripts.

For some other DBMS it is also easy to find a script that creates a date dimension. For SQL Server, I noticed http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx. This script was really helpful.
For MySQL, I found http://www.dwhworld.com/2010/08/date-dimension-sql-scripts-mysql/. This script was somewhat cumbersome but with some determination I got a nice table of dates in MySQL.

I also studied the scripts somewhat to see how they worked. The general idea is straightforward in the scripts.
The first step is to create a table. Let us give the an Oracle script:

CREATE TABLE Date_D(
DateKey Integer ,
DateValue Date ,
Day Char(10 ),
DayOfWeek Integer,
DayOfMonth Integer,
DayOfYear Integer,
PreviousDay date,
NextDay date,
WeekOfYear Integer,
Month Char(10 ),
MonthOfYear Integer,
QuarterOfYear Integer,
Year Integer
);

In a second step, a long table is created that only contains dates. In Oracle this can be done with a statement like:

select level
from dual
connect by level <= 5000;

This creates 5000 records ranging from 1 to 5000.

The 1, 2, 3 is subsequently translated into dates by:

select  TO_DATE('31/12/2009','DD/MM/YYYY') +
 NUMTODSINTERVAL(level,'day') datevalue
from dual
connect by level <= 5000

This provides a range of dates starting from 1 Jan 2010, 2 Jan 2010 etc .

This is stored in the target table with:

INSERT INTO Date_D(datevalue)
SELECT
datevalue
FROM (
select TO_DATE('31/12/2009','DD/MM/YYYY') + 
NUMTODSINTERVAL(level,'day') datevalue
from dual
connect by level <= 5000);

In a third step, extensive use is made of the date functions to derive the requested fields: the name of day, the weeknumber, the number of the month etc. One then updates the columns with the datevalue that is already loaded. As an example:

update date_d
set DateKey = to_number(to_char(datevalue, 'YYYYMMDD'));

and the remainder of the columns is updated with:

update date_d
set 
Day = TO_CHAR(datevalue,'Day'),
DayOfWeek = to_number(TO_CHAR(datevalue,'D')),
DayOfMonth = to_number(TO_CHAR(datevalue,'DD')), 
DayOfYear = to_number(TO_CHAR(datevalue,'DDD')),
PreviousDay=datevalue - 1,
NextDay = datevalue + 1, 
WeekOfYear = to_number(TO_CHAR(datevalue+1,'IW')),
Month = TO_CHAR(datevalue,'Month'), 
MonthofYear = to_number(TO_CHAR(datevalue,'MM')),
QuarterOfYear = to_number(TO_CHAR(datevalue,'Q')),
Year = to_number(TO_CHAR(datevalue,'YYYY'));

However, I had to create such a table on a Teradata DBMS. Unfortunately I could not find such a script for Teradata. Ok, I then created such a script myself from the date dimensions I created so far on Oracle, SQL Server and MySQL. Hereby I present you such a script with insert statements only. Click here to get the Script. Have fun with it. It only contains statements that Teradata understands: only numerics and strings.