Rob Joseph writes:
 
>How about character fields?  Typically these
>would be X6 or U6 coded as YYMMDD or even
>(as in our case), X4/U4 coded as YYMM.  My desire
>would be to see a function that would right justify
>these fields in a new field two characters larger
>and code the leading two positions with "19".
 
Although this posting will drift off of Alfredo's primary subject a bit, let
me absolutely, positively echo Rob's comments.
 
Indeed, because we manufacture a report writer, and because we help 10 to 30
people a day write their reports, we've come to see a great many databases.
The vast majority of the major applications in existence on the HP3000 use
text field dates. Moreover, I have come to strongly believe that only the
poorly designed databases use numeric fields to store dates.
 
If the character position of a numerical digit has even the remotest
possiblity of meaning something to someone, it is not merely a mistake to
store that number as a numeric field dataitem, it is a sin against God, man,
nature, machine and beast.
 
There is almost no simple way for the great majority of report writers in
existence to extract the middle two digits out of a numeric value such as
940501, especially when you wish to sum all of May data for the last 10
years, regardless of year or day value (##05##).
 
Our users have an escape however; they can create a detail list report in
QueryCalc that takes these individual numeric-form dates (corrected with an
offset, as is necessary when the original database date is given as a Julian
date from some arbitrary point, as with MANMAN), convert them into a
text-form date and /PRINTDB them to a new KSAM dataset that is built on the
fly, indexed and automatically entered into QueryCalc's dictionary, so that
the result of the first report may be immediately re-queried in a second
report.
 
While these steps are neither particularly complex nor time-consuming in
QueryCalc, the steps would never have to be performed if the database were
initially correctly designed. And without equivalent capabilities (which is
the common experience), parsing numeric items for their middle digits alone
ranges from extremely difficult to virtually impossible for most other report
writers.
 
My condemnation of designing databases that use numeric fields to store dates
does not end with dates alone, however. Any field where individual character
positions carry meaning (style, color, machine number, etc.) must be created
as a parsible field -- and that explicitly means a text field (X, U) in
IMAGE.
 
I consider any database that stores dates in a numerically encoded, Julian
date offset format to be "tricky" and "cute," the two surest signs of bad
design. Nor is conserving disc space an excuse for bad design. Any date that
is stored numerically will have to be converted into an essentially character
format upon retrieval by any program that has middle-digit search capacity in
order for it to search for only the middle character positions of the
retreived number. Any savings in disc space are being traded for CPU cycles,
which is a very poor trade indeed. CPU is almost always a more valuable
resource than the relatively small savings in disc space that accrue to a
numeric encoding.
 
Wirt Atmar