DOCUMENT #716
=======================================================================
WORKING WITH DATES
=======================================================================
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:
SET DATE MM/DD/YY
SHOW DATE
DATE format MM/DD/YY
DATE sequence MMDDYY
SET DATE 'MMM+ DD, YYYY'
SHOW DATE
DATE format MMM+ DD, YYYY
DATE sequence MMDDYYYY
SET DATE YY:MMDD
SHOW DATE
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:
input
/
SELECT * FROM transmaster WHERE transdate = 2/23/89
/
output
\
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
data.
SET VAR vdate DATE = 2/23/89
SHOW VAR vdate...
89/02/23
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
data.
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
returned?
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:
SET DATE MM/DD/YYYY
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.
SET DATE MM/DD/YYYY
UPDATE tblname SET datecolname = (datecolname + 693960) +
WHERE datecolname < 1/1/1900
SET DATE MM/DD/YY
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)).