The date data type holds a particular fascination to users of DATATRIEVE. I suppose this is so because such a data structure with the same capabilities does not exist in third generation programming languages. In fact, the powerful capabilities of DATATRIEVE's date data type are rarely found in fourth generation languages implemented on machines and operating systems other than VAX/VMS.
The underlying power of the date data type is due to the storage format that was chosen for it. Dates in DATATRIEVE are specified with a "USAGE IS DATE" clause and stored in exactly the same way the VMS system time and date are stored - as a 64-bit binary number. This quad word (four 2-byte words or eight bytes) is the number of 100 nanosecond "klunks" since midnight on November 17, 1858. This particular date was chosen because it is the beginning of the Smithsonian astronomical calendar. The valid range of the VMS (and thus DATATRIEVE) implementation of the Smithsonian calendar will be discussed later.
The obvious power of the date data type lies in the variety and flexibility of supported input and output formats. The default input format for numeric strings is controlled by the logical DTR$DATE_INPUT on the VAX and parameters in the customization build file QD.MAC on PDP-11's. For the VAX, the assignment
$ define dtr$date_input "MDY"
is the default, but "DMY" changes to the European style format and any of the six possible combinations ("DMY", "DYM", "MDY", "MYD", "YDM", "YMD") work but are not all useful. All of the input formats
DTR> declare day usage is date. DTR> day = "August 22, 1987" DTR> day = "Aug 22, 1987" DTR> day = "Aug 22 87" DTR> day = "Aug-22-87 DTR> day = "22 Aug 87" DTR> day = "22-AUGUST-87" ! or "22-august-87" case insensitive DTR> day = "8/22/87" DTR> day = "08/22/87" DTR> day = "08/22/1987" ! space, "-",",", and "." are also OK DTR> day = "082287" DTR> day = "08 22" ! defaults to the current year DTR> day = "08/22" ! defaults to the current year DTR> day = "August 22" ! defaults to the current year
are valid. But all of the following are invalid input formats:
DTR> day = "Au 22, 1998" ! 2 characters not allowed (June July) DTR> day = "1987 August 22" ! year can not come first DTR> day = "08221987" ! needs spaces, "/", ".", "-", or "," DTR> day = "08/22/1987" ! space, "-",",", and "."are also OK DTR> day = "0822" ! year can not be defaulted ! without space, "-", "/", ".", or "," DTR> day = "Augts 22, 1987" ! DATATRIEVE knows how to spell DTR> day = August 22, 1998 ! Quotes are required around date DTR> day = "August 22+ 1987" ! Only space, "-", "/", ".", and "," ! may appear with a date DTR> day = "November 16, 1858" ! Date before 11/17/1858 are not allowed, ! My PRO gives a conversion error but ! prints this date as " 6-May-11543" DTR> day = "January 1, 10000" ! December 31, 9999 is suppose to be the ! largest "legal" format, but see the ! discussion below on the limits of valid ! dates.
In addition to the formats listed above, there are four special input formats which are particularly convenient for entering dates. These are "today", "yesterday", "tomorrow", and "now". (Note that only "today" is available in DATATRIEVE-11. [Note added in 1997: However, the other three may have been added to DATATRIEVE-11 after 1987.]) They may be used as follows:
DTR> declare day usage is date. DTR> day = "today" DTR> print day DAY 22-Aug-1987 DTR-32> day = "now" DTR-32> print day using x(23) DAY 22-Aug-1987 18:35:27.15
(Note that the usual "DTR>" prompt is used when all types of DATATRIEVE apply. "DTR-11>" is used for code that applies to DATATRIEVE-11 only, and "DTR-32>" is used for those lines which apply to VAX DATATRIEVE only.)
|D||Each D is replaced by the corresponding digit of the date of the month. An edit string is DD is recommended.|
|M||Each M is replaced by the corresponding letter of the name of the month. An edit string is M(3) or M(9) is recommended.|
|N||Each N is replaced by the digit of the number of the month. NN is the recommended edit string.|
|Y||Each Y is replaced by the corresponding digit of the numeric year. Edit string of YY (for the two digit of year) or YYYY are recommended. [Note added in 1997: Obviously Y(4) is recommended for Y2K compliance.]|
|J||Each J is replaced by the corresponding digit of the Julian date. JJJ is the recommended edit string.|
|W||Each W is replaced by the corresponding letter from the name of the day of the week. W(9) is the recommended edit string.|
|X||X format is a special case which works differently in VAX DATATRIEVE and DATATRIEVE-11. In DTR-11, X(n) prints the first n characters of the date in the format DD-Mmm-YY (which is the default edit-string in DTR-11). In VAX-DTR, X(n) prints the first n characters of the date/time in the format DD-Mmm-YYYY hh:mm:ss.cc where the time is in hours, minutes, seconds, and hundredths of a second.|
In addition, a "B" is used to force a space and "/" (slash), "-" (hyphen), "," (comma), "." (period), and (on the VAX) quoted strings are allowed as they are in all edit strings. Some examples are
DTR> declare day usage is date. DTR> day = "today" DTR> print day using w(9),bm(9)bdd,byyyy DAY Saturday, August 22, 1987 DTR-32> print day using jjj"th day of "yyyy DAY 234th day of 1987 DTR-32> print day using wmywmydwmywmydwm DAY SA1au92tg8uu72rs ! DATATRIEVE is both smart and stupid at the same time
The most complicated output formatting is accomplished by the combination of CHOICE OF and FORMAT USING, as follows:
DTR-32> declare day usage is date CON-32> edit-string is m(9)bdd,byyyy CON-32> query-header is -. DTR-32> declare print-day computed by choice of CON-32> fn$day(day) lt 10 then format day using m(9)bd,byyyy CON-32> else format day using m(9)bdd,byyyy CON-32> end-choice CON-32> query-header is -. DTR-32> day = "1/1/87" DTR-32> print day, print-day January 1, 1987 January 1, 1987
This may seem like a lot of work just to get rid of one extra space, but in some applications that combine word processing and DATATRIEVE and extra effort may be worth the quality of the output.
In addition to input and output formatting, DATATRIEVE has some special capabilities known as "date arithmetic". Date arithmetic works differently in VAX DATATRIEVE and DATATRIEVE-11, but hte same results (calculations) can be accomplished.
The most common and useful date arithmetic is with birth dates and age.
DTR> declare date_of_birth usage is date. DTR> declare age computed by CON> fn$floor(("today" - date_of_birth)/365.24) CON> edit-string is zz0. DTR> date_of_birth = "11/8/72" ! My daughter Kathleen's birthday DTR> print date_of_birth, age DATE OF BIRTH AGE 8-Nov-1972 14
Of course, the second line (the one with the fn$floor) only works in VAX-DTR, but the function is only needed to truncate to the next lower age rather than rounding up to 15. To find out how many days until Christmas,
DTR> declare day1 usage is date. DTR> declare day2 usage is date. DTR> day1 = "today" DTR> day2 = "12/25/87" DTR> print day2 - day1 using zz9 125
There is, in fact, an inconsistency in date arithmetic in VAX DATATRIEVE which does not exist in DATATRIEVE-11. Under certain circumstances numbers added to or subtracted from dates in VAX-DTR may be in units of days or klunks. In DATATRIEVE-11, added or subtracted values are always in klunks. The following calculation is in klunks and works for both VAX DATATRIEVE and DATATRIEVE-11.
DTR> day1 = 0 DTR> print day1 DAY1 ! note that nothing is printed DTR> day1 = 1 DTR> print day1 DAY1 17-Nov-1858 DTR-32> print day1 using x(23) DAY1 17-Nov-1858 ! note that no time is printed because ! the time portion is too small DTR> print day1 using 9(18) DAY1 000000000000000001 DTR> day1 = 10000000 DTR-32> print day1 using x(23) DAY1 17-Nov-1858 00:00:01
There are certain arithmetic operations such as addition, multiplication, and division of dates which DATATRIEVE will resist. However, if one equates a date to a quad data type either through a REDEFINES clause or a direct arithmetic assignment, these operations can be accomplished and are, in fact, useful.
DTR> declare day1 usage is date. DTR> declare day2 usage is date. DTR> day2 = 0 DTR> day1 = "today" DTR> print day1 - day2 47029 ! the difference is in days, not klunks! DTR-32> declare qday2 usage is quad. DTR-32> qday2 = day1 DTR-32> print qday2/864000000000 47029
Or with the following record definition
DEFINE RECORD TEST_RECORD USING 01 TEST_REC. 03 TDATE USAGE IS DATE. 03 RDATE REDEFINES TDATE. 05 QD USAGE IS QUAD. ; DTR> find test [3 records found] DTR> print test TDATE 23-Aug-1087 22-Aug-1987 24-Aug-1987 DTR-32> print average qd using dd-mmm-yyyy AVERAGE ! so it is possible to do arithmetic QD ! operations on date as long as you ! work on them as QUAD data type and 23-Aug-1987 ! then convert them to dates for output DTR> store test ! You can even manage the time part Enter TDATE: 25-aug-1987 DTR> find test [4 records found] DTR-32> print average qd(-) using dd-mmm-yyyy, fn$time(average qd)(-) 23-Aug-1987 12:00:00.00
There are, of course, other date functions in VAX DATATRIEVE. These are FN$JULIAN, FN$WEEK, FN$YEAR, FN$MONTH, FN$DAY, FN$TIME, FN$HOUR, FN$MINUTE, FN$SECOND, FN$HUNDREDTH, and FN$DATE. Only the last of these requires some special attention. It is not possible to have both a COMPUTED BY and a USAGE IS clause in the same field or global declaration. Therefore, in COMPUTED BY date variables it is necessary to use the FN$DATE function to force a variable to assume date data type. There is a particular idiosyncrasy of this function which requires its input to be in upper case. That is, fn$date("22-Aug-1987") fails with a conversion error, but fn$date("22-AUG-1987") and fn$date(fn$upcase("22-Aug-1987")) works just fine.
The largest date which is "legal" in DATATRIEVE is an interesting point. Here VAX DATATRIEVE and the various versions of DATATRIEVE-11 (RSTS, RSX, and PRO) differ. For the VAX, the documentation states that the largest "legal" date is December 31, 9999. However, dates larger than "December 31, 3000" get data conversion error on input and are not correctly converted to their internal format. If, however, one does some date arithmetic on date data type, one can get correct output of dates that have been previously stored all the way up to December 31, 9999. That is, for VAX DATATRIEVE output formatting and date arithmetic work through December 31, 9999. For DATATRIEVE-11 (in this case PRO-DATATRIEVE V2.0), date arithmetic and date input work until the date December 25, 31085. However, output of the date breaks down somewhere after the year 30001. Thus, for DATATRIEVE-11 the following occurs:
DTR-11> declare day usage is date. DTR-11> day = "1/1/30001" DTR-11> print day DAY 1-Jan-30001 DTR-11> day = "12/25/31985" DTR-11> print day DAY 24-Dec-3108 DTR-11> day = "12/26/31085" Conversion error from date string "12/26/31085"
The upper limit of the usefulness of the date data type is important only for some astronomical data bases. But there is a phenomenon which may invalidate the DATATRIEVE date conversion long before the year 9999. To understand this we need to look carefully at the following calculation:
DTR> declare day usage is date. DTR> declare qday usage is quad. DTR> qday = 1 DTR> repeat 64 begin CON> day = qday CON> print qday, day using x(23) CON> qday = qday * 2 CON> end QDAY DAY 1 17-Nov-1858 ! 100 nanoseconds past midnight 2 17-Nov-1858 . . . . . . 8388608 17-Nov-1858 16777216 17-Nov-1858 00:00:01.67 !past 1 second past midnight 33554432 17-Nov-1858 00:00:03.35 . . . . . . 536870912 17-Nov-1858 00:00:53.68 !past 1 hour past midnight 1073741824 17-Nov-1858 00:01:47.37 . . . . . . 549755813888 17-Nov-1858 15:16:15.58 1099511627776 18-Nov-1858 06:32:31.16 !second day 2199023255552 19-Nov-1858 13:05:02.32 . . . . . . 18014398509481984 18-Dec-1915 23:57:30.94 !World War I 36028797018963968 17-Jan-1973 23:55:01.89 72057594037927936 20-Mar-2087 23:50:03.79 !100 years from now 144115188075855872 24-Jul-2315 23:40:07.58 288230376151711744 29-Mar-2772 23:20:15.17 576460752303423488 9-Aug-3685 22:40:30.34 1152921504606846976 4-May-5512 21:21:00.68 2305843009213693952 20-Oct-9165 18:42:01.36 4611686018427387904 22-Sep-**** 13:24:02.73 0 !back to 17-Nov-1858
We can see from this little exercise that the DATATRIEVE-11 date type works over the full rang of the 64-bit binary number and goes all the way to December 25, 31085. But this wide range4 brings up the problem of Leap Years. There are, on the average, 365.2422 days per year. [Note added in 1997: Actually there are 365.24219 days per year.] To keep the date of the first day of Spring within one day, it is necessary to have Leap Year. This is, the year has one extra day on February 29th. The Leap Year rules are:
And I believe that there is, or should be, a 3200 year rule and a 25600 year rule, but I could not find them in any standard encyclopedia. Thus, I believe, that the following calculation
DTR-32> declare year pic 9999. DTR-32> declare day1 usage is date. DTR-32> declare day2 usage is date. DTR-32> year = 1900 DTR-32> repeat 100 begin CON-32> day1 = fn$date("28-FEB-"|year) CON-32> day2 = day1 + 1 CON-32> print year, day1, day2 CON-32> year = year + 100 CON-32> end YEAR DAY1 DAY2 1900 28-Feb-1900 1-Mar-1900 2000 28-Feb-2000 29-Feb-2000 2100 28-Feb-2100 1-Mar-2100 2200 28-Feb-2200 1-Mar-2200 . . . 3000 28-Feb-3000 1-Mar-3000 3100 28-Feb-3100 1-Mar-3100 3200 28-Feb-3200 29-Feb-3200 <-- I believe this should not be a Leap Year. 3300 28-Feb-3300 1-Mar-3300 . . .
the VMS and VAX DATATRIEVE treat the year 3200 as a Leap Year and I believe that it should not be a Leap Year. Therefore, I would not trust any dates past February 28, 3200 as being correct. VAX input also stops at December 31, 3000. This is, of course, an amazingly long time in the future. [Wonder what version of VMS they will be running in the year 3200?!?!?!] [Note added in 1997: When this article was originally written in 1987, I asserted that there should be at 3200 year rule and that the year 3200 should not be a Leap Year. In the rules for the Gregorian Calendar, there is no 3200 year rule. And Leap Seconds only increase the need to have a 3200 year correction rule.]
DTR> declare entry-date usage is date CON> valid if entry-date le "today" and entry-date + 15 ge "today" . DTR> entry-date = "tomorrow" Validation error for field ENTRY_DATE. DTR> entry-date = "8/22/96" Validation error for field ENTRY_DATE. DTR> entry-date = "8/6/87" Validation error for field ENTRY_DATE. DTR> entry-date = "yesterday" ! Today is 8/22/97. DTR> print entry-date ENTRY DATE 21-Aug-1987
Well, you now know almost everything that there is to know about DATATRIEVE date date type. But I'll bet that if you attend the Wombat Magic Sessions at future DECUS Symposia or carefully read the transcriptions of those sessions in the Newsletter, you will find some innovative ways of solving a problem with date data type.
Joe H. Gallagher, Ph. D.