Focus supports a wide variety of date
formats. Two basic classes of dates in
Focus are
alphanumeric and integer dates and smart
dates.
Alpha numeric and integer formats with Date edit options .
FIELD=DATE1, , USAGE=A6YMD
,ACTUAL=A6,$
FIELD=DATE2, , USAGE=A8YYMD ,ACTUAL=A8,$
FIELD=DATE3, , USAGE=I6YMD,
,ACTUAL=I6 ,$
When specified this way Focus stores the field as an Alpha or integer value as the case may be, however the display options can be changed as required. For instance if DATE1 has a value of 980302, it can be displayed as MARCH 2 1998, with the appropriate edit option.
DEFINE DATE_TMP/A6MTDYY=DATE1;
PRINT DATE_TMP;
or with
DEFINE DATE_TMP/A2MT=DATE1;
PRINT DATE_TMP;
Gives MARCH;
and /Q gives Q1. For quarter 1.
However the limitation of defining a
date field as an Alpha or Integer Format is that it is
stored internally as the value entered, like
980302. If date calculations are involved like
adding 30 days to the stored date, Focus
treats these calculations as regular Arithmetic and
and not as dates , hence the appropriate date
is not calculated or is in error.
FOCUS provides another option, that of defining a
field as a date field , then FOCUS treats this
as a date and not as an alpha or numeric field
which happens to be a date.
How such date formats are stored internally in Focus.?
Here the date is stored as an four byte binary
integer representing the elapsed days from a
base date. This base date is December 31, 1900. For instance
1 Jan 1902 would be stored as
366, as this is the number of days elapsed from the
base date. When this date is displayed however, it is displayed as
1902/01/01. (If edit option is YMD).
Advantages of smart dates .
This enables one to define a field as a date and manipulate the field's
value in
ways that may be required for a date. These include how a date is displayed
and stored.
The features that Focus provides are
· Define date components such as year, Quarter, Month
etc.
· Do arithmetic with dates and
compare dates without resorting to special date handling routines.
· Sort on date regardless of how
a date is displayed.
· Refer to dates in a natural
way , such as Feb 29, 2000. Without regard to display
or edit
options specified.
· Automatically validate dates in transactions.
To specify a date format in the MFD,
FIELD=DATE1, , USAGE=YMD
, ACTUAL=A6,$
FIELD=DATE2, , USAGE=YYMD , ACTUAL=A8,$
FIELD=DATE3, , USAGE=MDY
, ACTUAL=A6,$
This ensures that Focus treats the fields
DATE1, DATE2, DATE3 as date fields and
the days offset from the Focus base date is stored
internally. The various edit options
we can use with dates are described in Focus-on-dates.
(Like MTRDYY etc...)
Using date Format data :--
Date format data is automatically validated when entered
. It can be entered as
a natural date literal, (like March 12, 1998).
Focus does the conversion and stores the days
offset.
Computations with date literal
In WHERE screening conditions
WHERE DATE1 IS ‘APR 25 ,2001’
In arithmetic expressions. DATE1 -
‘29 FEB 2000’
Note that the date field defined with
a date format can be entered with any format,
DATE1 defined in the MFD as YMD can be entered as
‘1990 , 25 JAN’ Focus does the conversion.
This is where the difference between numeric (or
alphanumeric ) date literal with natural
date literal becomes apparent. Numeric date literal are
stored as a simple string of digits
and so the order of date components must match
the order of the USAGE specification
for it to be accepted. For Eg, if the USAGE is DMY
then MARCH 3, 1998, must be
represented as 030398 only.
Some other features of Date fields ( smart dates
) :--
· A smart date can be defined with the USAGE format
as MDY or YMD, and a value as 2000/02/29 can be
entered ( for YMD format ) .It is accepted and valid. This is not
the case with numeric date literal , for accepting 2000/02/29
the USAGE= must be DEFINEd as YYMD, else it is
not accepted.
· For a USAGE = format of YMD,
if the century is not specified, it defaults to
19.
· Since the internal storage is the
days offset and not the numeric value, a smart
date can be
sorted ( with a BY or ACROSS ) correctly
with the right century even if the USAGE
format is MDY ( and not MDYY as in case
of numeric date formats). Provided of course that
the century is specified while entering the value.
Eg.
DEFINE FILE xxxxx
DATE2/A8YYMD= EDIT(DATE3);
DATE1/YMD= DATE2;
TABLE FILE xxxxx
SUM SALES1 SALES2
BY DATE1
END
This will result in sorting with the correct century
provided that DATE3 is with
the correct century in the database.
The following is a list of the various date formats that FOCUS allows and their corresponding display formats.