|
I would start by seeing if someone else has dealt with this problem and if they
are willing to share their solution with you. Do a Web search and also a search on Oracle's MetaLink site, asking about the proper name of the calendar. If you don't find anything, post a question on the "Oracle PL/SQL" forum in MetaLink. (I recommend the PL/SQL
forum because I would guess that the solution, like mine below, involves PL/SQL.)
Assuming you don't find anything already written, you'll have to create it.
You may need these features:
- Conversion routines between your calendar (which I'll call M30 from now on)to the Gregorian calendar already implemented by Oracle.
- SYSDATE function
- Date arithmetic (DATE + NUMBER = DATE and DATE - DATE = NUMBER)
- Optional storing of time of day
- Day-of-the-week functions (like TO_CHAR (dt, 'D')) if M30 uses a seven-day week
Since Oracle already does everything (except the conversions), I would store the dates in an Oracle DATE column (or, maybe, a user-defined TYPE with a DATE attribute), and let Oracle do the rest of the work. You just call the conversion functions when you need to read or write an M30 date. For example, when you collect an appointment date and a follow up date, which defaults to six weeks (42 days) after the appointment date. Collect the data as a strings, in m30 'YYYY-MM-DD' format. When you create a record, you say
INSERT ...
(... appt_dt, follow_up_dt, ... )
VALUES (... from_m30 (appt_text), NVL ( from_m30 (follow_up_text),
from_m30 (appt_text) + 42
), ...
) ...
When you display the data:
SELECT ...
to_m30 (appt_dt) AS appt_text,
to_m30 (follow_up_dt) AS follow_up_text
...
Users never have to see a Gregorian date, and you never have to do computations (other than conversions) on an M30 date.
The function from_m30 (next page)
takes a VARCHAR2 argument representing the m30 date (YYYY-MM-DD format) and returns a DATE. The function to_m30 takes a DATE and returns the corresponding string.
I'm confused by your description of M30. If every M30 year starts on April 21, then the year will contain 365 or 366 days. The functions below assume that an M30 year contains 13 months: 12 months of 30 days followed by one month of five or six days. (If the M30 year really does consist of only 360 days, and the New M30 Year's Day is April 21 this year, April 16 next year, etc., the interface would be the same: the functions would just be a lot messier.) You didn't say anything about how years are numbered in M30: I'll assume they differ
from Gregorian years by some constant. The functions below arbitrarily assume that the M30 date '0001-01-01' corresponds to the Gregorian date '0079-04-21'
Continued in part 2.
|
