Re: Calculating the first day of our fiscal year.

I use a table similar to the following:

CalendarDay DATE,
Year NUMBER(4,0),
FiscalYear NUMBER(4,0),
QuarterOfYear (1,0),
MonthOfYear (2,0),
DayOfMonth (2,0),
DayOfFiscalYear (3,0),
etc....

I then populate this table with a few thousand rows for all days of every year for the next 20 years (or however long you expect your app to be in use). Then, each of those "quality" fields are indexed using a traditional B-tree index.

This then allows your app to very quickly determine the attributes of any given calendar day by doing a join to this table. You can also easily pull all the rows from an orders table (for example) that are in quarter 3 of 1997 by doing a join of the order date to this table and selecting that quarter/year combination. This way your code never has to have the calculations for your particular calendar/accounting calendar/sales calendar, they only exist in the procedure that populates this table.

One other benefit is that you can easily handle "special case" days, like when the accounting department tells you that the first three days of April are going to be considered to be part of the first quarter, but only this year and next year. Instead of building some complex hairy conditional logic into every program you write from now until the end of time, you just manually update those three rows in this table and every program takes those special days into account.

Just my 2 cents.

Chris wrote:
>
> I'm not too crazy about hard-coding dates into my sql programs. What I was
> wondering is if anyone has seen or is using an equation that calculates the
> first day of a certain month(in my case april) and will change automatically
> when the date passes this date. I also need it to keep last years year and
> date until this years april 1st date is passed.
> I hope this makes sense
>
> Thanks in advance.
> Chris
Received on Fri Apr 10 1998 - 19:37:15 CDT