Much of my job has to do with accounting and reporting. Exciting stuff, right? At any rate, it is often useful to denormalize the dates in often-queried tables to both increase performance and ease of querying. That is, we take some date field (I tend to use smalldatetime on SQL server because most of what I do does not need sub-second resolution), in my examples called When_Date and crack it open into its constituent parts. Depending on your needs, this might include the Year, Month, Day of Month, Day of Year, Week of Year, Time, Hour of Day, and whatever else your crazy users can think of.
Instead of worrying about updating a dozen fields each time I update a date, I use a trigger to take the one canonical date field and denormalize it, like so:
CREATE TRIGGER [date_fix] ON dbo.TimeEntry_Log
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF UPDATE(When_Date) BEGIN
UPDATE TimeEntry_Log SET
When_Day = DATEADD(DAY,DAY(When_Date)-1,DATEADD(MONTH,MONTH(When_Date)-1,DATEADD(YEAR,YEAR(When_Date)-2000,{d '2000-01-01'}))),
When_Mon = DATEADD(MONTH,MONTH(When_Date)-1,DATEADD(YEAR,YEAR(When_Date)-2000,{d '2000-01-01'})),
When_MOY = MONTH(When_Date),
When_Year = YEAR(When_Date),
When_Time = DATEADD(YEAR,2000-YEAR(When_Date),DATEADD(MONTH,1-MONTH(When_Date),DATEADD(DAY,1-DAY(When_Date),When_Date))),
When_Hour = DATEPART(HOUR,When_Date),
When_WOY = DATEPART(WEEK,When_Date),
When_Week = DATEDIFF(WEEK,{d '2000-01-01'},When_Date)
WHERE (id IN (SELECT id FROM inserted))
END
SET NOCOUNT OFF
Of course, you would have an entirely different set of fields, but the concept is the same. In this case, we might take today and break it down thusly:
| Column | Type | Value | Comments |
|---|---|---|---|
| When_Date | smalldatetime | {ts ‘2006-01-24 16:43:32’} | The original value |
| When_Day | smalldatetime | {d ‘2006-01-24’} | No HH:mm:ss (day granularity) |
| When_Mon | smalldatetime | {d ‘2006-01-01’} | Month-level granularity |
| When_MOY | tinyint | 1 | Month of year |
| When_Year | smallint | 2006 | Year |
| When_Time | smalldatetime | {ts ‘2000-01-01 16:43:32’} | Time component only |
| When_Hour | tinyint | 16 | Hour |
| When_WOY | tinyint | 4 | Week of year |
| When_Week | smallint | 3 | Weeks since our epoch |
These are just the ones that I use regularly. There are plenty more: quarter, time-of-day rounded to the nearest 15 minutes, fiscal year, etc.
The great thing about a system such as this is that not only does it make for faster, easier queries, but it also tends to make your ColdFusion code easier to read. Instead of building heinous DATEPART statements into your code, you can just choose the correct column. No muss, no fuss.