PRODUCT:  R:BASE                   VERSION :  3.1 or Higher
     CATALOG:  Data Manipulation        AREA    :  Dates  
     R:BASE has two settings for date handling, SEQUENCE and FORMAT, for 
     data input and output respectively. These two settings facilitate 
     accessing data using date columns.
     Date Output Format
     The date FORMAT displays (output) dates. There are many options for 
     the format from the default of MM/DD/YY (12/31/92) to 
     WWW+, MMM+ DD, YYYY (Thursday, December 31, 1992). The FORMAT does 
     not need to contain all parts of the date, it can just be MMMYYYY 
     (Dec1992) for example. The FORMAT is how R:BASE shows you date values.
     Date Input Sequence
     The date SEQUENCE is used for entry (input) of date values. This is 
     the order of month, day and year R:BASE expects when date values are 
     entered. The default is MMDDYY. The SEQUENCE never has delimiters. It 
     does not need to include all parts of the date, it can just be MMYY 
     (the day is defaulted to 01).
     Working Together
     The date SEQUENCE and FORMAT settings work together to interpret date 
     data. They can be set individually; however, the SEQUENCE order 
     normally matches the FORMAT order. To set the SEQUENCE and FORMAT 
     together with one command, use SET DATE  at the R> prompt. 
     For example:
      DATE format     MM/DD/YY
      DATE sequence   MMDDYY
      DATE format     MMM+ DD, YYYY
      DATE sequence   MMDDYYYY
      DATE format     YY:MMDD
      DATE sequence   YYMMDD
     The single SET DATE command sets the FORMAT and then the SEQUENCE to 
     the order specified by the FORMAT. By using SET DATE instead of the 
     separate SET DATE FORMAT and SET DATE SEQUENCE commands, the two will 
     always match. Use SHOW DATE to view the current FORMAT and SEQUENCE 
     settings. Here's an example from the CONCOMP sample database showing 
     how the FORMAT and SEQUENCE work together:
       SELECT * FROM transmaster WHERE transdate = 2/23/89
       transid   custid   empid   transdate   netamount    freight   ......
       -------   ------   -----   ---------   ----------   --------  
       4800      105      160     02/23/89    $167250.00   $1672.50 
       4865      102      129     02/23/89    $29125.00    $291.25
     If the date sequence and format do not match, the results can be 
     unexpected and unsatisfactory. This is a case where valid commands 
     can return invalid results.
     Unmatched Date Sequence and Format 
     In the following example, the order of the date FORMAT and SEQUENCE 
     don't match. The FORMAT has the year first, the SEQUENCE expects the 
     year last. A date value entered to match the SEQUENCE will return 
     results, however. Notice that the dates are displayed with the year 
     first to match the FORMAT.
       SET DATE FORMAT yy/mm/dd  
       SET DATE SEQUENCE mmddyy
       SELECT * FROM transmaster WHERE transdate = 2/23/89
       transid  custid  empid  transdate  netamount    freight   ......      
       -------  ------  -----  ---------  ----------   -------- 
       4800     105     160    89/02/23   $167250.00   $1672.50 
       4865     102     129    89/02/23   $29125.00     $291.25
     Entering a literal value that matches the date SEQUENCE retrieves 
     data. Storing the date comparison value in a variable does not return 
       SET VAR vdate DATE = 2/23/89
       SHOW VAR vdate...
     The date placed in the variable is entered to match the SEQUENCE, 
     but the variable value displays matching the FORMAT. When the 
     variable is then used in a command, the input value (vdate) does 
     not match the SEQUENCE returning an error message. The SEQUENCE 
     expects an order of MMDDYY, the variable value is 89/02/23, YYMMDD.
       SELECT * FROM transmaster WHERE transdate = .vdate
       -ERROR- Invalid DATE value
     Other errors that can indicate mismatched date FORMAT and SEQUENCE 
     are "-ERROR- Column transdate must be compared to DATE values." and
     "-ERROR- Your value does not have the same type or scale as your 
     variable." R:BASE does not always return an error message when the 
     date FORMAT and SEQUENCE don't match. Sometimes it just doesn't find 
     First Century Dates
     The command SELECT * FROM transmaster WHERE transdate = 2/23/89 
     returns "No rows exist or satisfy the specified clause" when the 
     date SEQUENCE is set to a 4-digit year (MMDDYYYY). The data displays 
     rows where the transdate column has the value 02/23/89. Why isn't data 
     The date value in the WHERE clause is in the correct SEQUENCE (month, 
     day year), but the SEQUENCE expects to find 4 digits entered for the 
     year. The input value only has 2 digits so R:BASE fills in the other 
     2 digits; but it doesn't fill them in with 19 (the current century) 
     it fills them in with 00 (the first century) the default. The command 
     above is thus comparing transdate to 02/23/0089, not 02/23/1989, and 
     doesn't find any matching data. The same is true if the comparison 
     value is a literal as shown or a variable.
     In the above example, the data is stored correctly in the table; the 
     comparison value is being interpreted as the incorrect date. The 
     problem could be just the reverse; the data stored in the table could 
     be the wrong century. Because of the 4-digit year in the date SEQUENCE, 
     when only 2 digits are entered for the year in a new data record, the 
     data is stored as a first century date (0092), not a twentieth century 
     date (1992).
     When the date FORMAT is set to display 2 digits for the year, looking 
     at the data does not identify whether the problem is in the data or in 
     the comparison value. Use the following commands to see if data is 
     stored with first century dates:
       SELECT transdate FROM transmaster WHERE transdate < 1/1/1900
     If the rows returned show a year of 0092, not 1992, then they are 
     stored as first century dates and need to be converted to twentieth 
     century dates.
     Converting Dates From The First To The Twentieth Century
     To convert dates from the first century to the twentieth century, add 
     to the affected dates the number of days difference between a date in 
     the twentieth century and the same date in the first century. Taking 
     leap years into account, that number is 693960. 
     The following commands convert all first century dates in the 
     specified column to twentieth century dates:  The date FORMAT and 
     SEQUENCE must both be set to a 4-digit year for this to work.
       UPDATE tblname SET datecolname = (datecolname + 693960) +
        WHERE datecolname < 1/1/1900
     If your data has dates that are supposed to be in another century 
     (such as archaeological or historical dates), modify the WHERE clause 
     given above to look for dates prior to 1/1/1900 that should not exist 
     in your data. 
     Where did the dates come from?
     Determining the cause of the problem with the dates prevents it from 
     repeating. Some common ways that these first century dates can occur 
     in your data are:
     1.  If the date sequence is set to MM/DD/YYYY and a 2-digit year 
     (such as 5/23/46) is entered, the first 2 digits of the year are 
     automatically be assumed to be 00, the default. R:BASE does not 
     assume the current century from the system date when the date 
     sequence is set to a 4-digit year. If, for some process, you 
     temporarily change the sequence to MM/DD/YYYY, be sure to change 
     it back to MM/DD/YY before disconnecting the database.
     2.  The R:BASE UNLOAD or BACKUP command puts several SET commands 
     in the output along with the data. These R:BASE commands set the date 
     SEQUENCE to MM/DD/YYYY at the beginning and then reset it to MM/DD/YY 
     (or the current setting) at the end. When loading one of these backup 
     or unload files, if you interrupt (abort) the loading process, the date 
     sequence is not reset. The next time you enter data, you need to enter 
     the complete year (5/30/1987) or the century will be off. The following
     guidelines below avoid this:
     -- Do not interrupt loading or unloading processes.
     -- Check (or automatically set) the date sequence before entering data.
     -- If you do interrupt an UNLOAD (that is being OUTPUT to a file) or 
        BACKUP command, edit the resulting data file to include SET DATE 
        MM/DD/YY at the bottom.
     -- If you do interrupt a RESTORE, RUN, or INPUT command (in the process 
        of loading a file that contains date setting commands), immediately 
        set the date to the sequence you usually expect (for example, SET 
        DATE MM/DD/YY).
     -- Use the UNLOAD...AS ASCII command instead of the BACKUP or other 
        UNLOAD commands. When you use the AS ASCII clause only data in an 
        ASCII delimited format is output to the file so the date SEQUENCE 
        is not altered.
     3.  If you use the RDATE function (which REQUIRES a 4-digit year), and 
     use a 2-digit year as a parameter (such as (RDATE(5,23,46))), the first 
     two digits of the year are assumed to be 00. To avoid this, use a 4-
     digit year or add 1900 to the year parameter:  
       (RDATE(.vparam1,.vparam2,(1900 + .vparam3)).