Developing Applications in DATATRIEVE
The Date Data Type

Joe H. Gallagher, Ph. D.



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.)

Edit String
Character
Description
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.




DTR-32> declare day usage is date. 
DTR-32> day = "today"
DTR-32> print day

    DAY

22-Aug-1987

DTR-32> day = day + 30 
DTR-32> print day

    DAY

21-Sep-1987

DTR-32> day = "today" 
DTR-32> day = day + (-30) 
DTR-32> print day

    DAY

23-Jul-1987 
 
DTR-11> declare one-day pic 9(18). 
DTR-11> one-day = 864000000000 
DTR-11> declare day usage is date.
DTR-11> day = "today" 
DTR-11> print day

   DAY

22-Aug-87

DTR-11> day = day + 30 * one-day 
DTR-11> print day

   DAY

21-Sep-87

DTR-11> day = "today" 
DTR-11> day = day + 30 * (- one-day)
DTR-11> print day

   DAY

23-Jul-87 

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:

  1. Years which are evenly divisible by 4 are Leap Years.
  2. Years which are evenly divisible by 100 are not Leap Years.
  3. Years which are evenly divisible by 400 are Leap Years.

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.


Originally published in the newsletter of the DATATRIEVE/4GL SIG, The Wombat Examiner and 4GL Dispatch, Volume 9, Number 4, pages 2-7; in the Combined SIGs Newsletters of Digital Equipment Computer Users Society, Volume 3, Number 4, December 1987.
Joe H. Gallagher, Ph. D.
dtrwiz@ix.netcom.com
MAILTO

BACK Back