>
>Date:    Fri, 18 Jul 1997 15:07:17 +0100
>From:    Philippa Tinsley <[log in to unmask]>
>Subject: dates 2000 or otherwise in automated systems
>
>In >>> Posting number 29182, dated 16 Jul 1997 00:00:0/
>Orycteropus afer <[log in to unmask]> wrote:
>
>Far too many automated systems define the fields
>used to record dates (acquisition dates, dates of manufacture, =
>conservation
>dates, movement dates, etc.) as what is called a "date" type field =
>internal
>to the database management system....This is almost always a BIG =
>mistake..

Then Philippa responded:
>
>I'm sorry, but I absolutely have to disagree with this...
>
>I believe that the reason for having a computerised collection =
>management system is to help you more effectively and efficiently manage =
>your collection.  To this end, it is important to utilise the database's =
>searching facilities as well as possible.
>
>For example...
>I want to get a list of all objects donated to the museum in the (UK) =
>tax year 1995/96
>
>If the field 'date acquired' is a date field, I can simply search for =
>everything from 6/4/1995 to 5/4/1996 and the system will find them all.
>
>However, if 'date acquired' is a character field (however manipulated), =
>I have to search for the following sets of characters '4/1995', =
>'5/1995', '6/1995', '7/1995', '8/1995', '9/1995', '10/1995', '11/1995', =
>'12/1995', '1/1996', '2/1996', '3/1996', '4/1996'.
>Then I have to go through all the objects found by this search and =
>discount those with the characters '1/4/1995', '2/4/1995', '3/4/1995', =
>'4/4/1995', '5/4/1995', '6/4/1996' etc.
>If I also included some objects in my database just with the year of =
>accession '1995', I'll have to make a separate search for these and =
>decide individually if I want to include them in my list.
>
>
>Philippa Tinsley
>
>[log in to unmask]
>[log in to unmask]
>
RESPONSE:

If the masked date field is recorded in descending order of CCYY-MM-DD then
the searching is as easy as it is with a date type field.  Obviously if it's
recorded as DD-MM-CCYY, it's not.

Additionally, in this age of open systems where you can purchase
off-the-shelf utilities and packages such as report generators and work flow
systems that attach to standard SQL-based systems such as Access, Oracle,
and SQL Server, you have to be careful to make sure that you don't overly
program a field in a museum automation system.  For example, some systems
store measurements in a "base" system of something say like millimeters.
Then the system converts out to Imperial measurements (say inches) on the
fly and displays them on the screen.  However, this capability isn't likely
to be available in an off-the-shelf report generator that you want to add to
your system and as a result you can't include Imperial measurements in your
report because they don't actually exist in the database as a field that the
report generator can incorporate into a report.

Similary with dates, some systems have elaborate programs incorporated into
their structure that concatenate multiple fields to create a display field
(such as a numerical date format and a certainty field like ca. or probably
and these certainty fields are often encoded).  This can't be easily
replicated with off-the-shelf systems so you cut yourself off from
everything but the hard programming in your system which means you have to
then do more hard programming for reports, for the Internet, etc.

So, the rule of thumb is to keep it simple in the database and avoid using
macros and decoder tables.  This is particularly important as we go into
intranet and Internet applications for public learning.