4-4-5 Accounting Periods are the Bane of my Existence

Question

We use 4-4-5 Accounting Periods. In case you aren't familiar with this, you can find info at Wikipedia.

To accomplish this, I created a date table with all the days for the next 10 years. I used the script (albeit modified) found here.

The creation script for my table is:

USE [RedFridayDates];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [tblRedFridayAllDates] (
[DATE_ID] int NOT NULL,
[DATE] datetime NOT NULL,
[YEAR] smallint NOT NULL,
[MONTH] tinyint NOT NULL,
[DAY_OF_WEEK] tinyint NOT NULL,
[REDFRIDAY] bit NULL,
[Period] tinyint NULL,
[ForecastSales] money NULL)
ON [PRIMARY];
GO

The Period is my 4-4-5 "month".

I typically link my queries to it with the following template I created:

SELECT RED.[YEAR] as [Year],
RED.PERIOD as [RF Period],

FROM TableName
  INNER JOIN REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED
          ON RED.date =
             CAST (FLOOR (CAST (TableName.Date AS FLOAT)) AS DATETIME)

Most of my database usage is SQL 2000 so my dates are all datetime fields.

Is there a more efficient way to accomplish this? Is this the best Query template I could use? What about other ways to convert Date to Date time? Are there faster ways to do so?

Answer 1

I have used extracting the date part of the datetime into a persisted computed field (maybe indexed) and do all joins and searches on the computed field (date only). In effect you'd have the 2k8 new DATE type in pre-2k8, at the expense of 8 bytes (or even 4 bytes if you're willing to store it as int). The advantages is SARGability of datetimes by only date part.