If you have ever worked with a data warehouse, or a complex, date-sensitive application, then you have probably come across a date dimension table. Simply put, a date dimension table is a lookup table that contains one record per date. Each record has information about that date, such as the day of the week, the quarter it falls in, and so on. Many DBAs come armed with a SQL script that creates and populates a date dimension table. If you are not a SQL adept, why not use the power of ColdFusion?

Each record in a date dimension table describes a date, so our table will have the following columns:

  • date - the full date
  • year - the date's year
  • month - the date's month number
  • month_desc - the month's name
  • day - the date's day
  • quarter - the date's quarter within the year
  • day_of_week - the date's day of week number
  • day_of_week_desc - the day of week's name
  • day_of_year - the date's day number within the year
  • week_of_year - the date's week number within the year

In SQL Server:

view plain print about
1CREATE TABLE days (
2    [days_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
3    [date] smalldatetime NOT NULL,
4    [year] tinyint NOT NULL,
5    [month] tinyint NOT NULL,
6    [month_desc] varchar(10) NOT NULL,
7    [day] tinyint NOT NULL,
8    [quarter] tinyint NOT NULL,
9    [day_of_week] tinyint NOT NULL,
10    [day_of_week_desc] varchar(10) NOT NULL,
11    [day_of_year] tinyint NOT NULL,
12    [week_of_year] tinyint NOT NULL
13)

Now that the table is created, it needs data. Populating the data is simply a big loop using the many different CF functions for splitting and formatting dates. In this case, I will generate a script that can then be run in a database query tool, such as Query Analyzer:

view plain print about
1<cfset currentDate = createDate(2000, 01, 01)>
2<cfset targetDate = createDate(2008, 12, 31)>
3
4<cfloop condition="dateDiff('d', targetDate, currentDate) LTE 0">
5    <cfoutput>
6        INSERT INTO days (date, year, month, month_desc, day, quarter, day_of_week, day_of_week_desc, day_of_year, week_of_year)
7        VALUES (
8            '#createODBCDate(currentDate)#',
9            #year(currentDate)#,
10            #month(currentDate)#,
11            '#monthAsString(month(currentDate))#',
12            #day(currentDate)#,
13            #quarter(currentDate)#,
14            #dayOfWeek(currentDate)#,
15            '#dayOfWeekAsString(dayOfWeek(currentDate))#',
16            #dayOfYear(currentDate)#,
17            #week(currentDate)#
18        )
19    </cfoutput>
20    <cfset currentDate = dateAdd("d", 1, currentDate)>
21</cfloop>

In the above example, the date dimension table will contain one record for each day between January 1, 2000 and December 31, 2008. In the real world you will probably use a longer timespan, depending on your needs.