
This article is reprinted from the December 1990 edition of TechNotes/dBASE
IV.  Due to the limitations of this media, certain graphic elements such as
screen shots, illustrations and some tables have been omitted.  Where
possible, reference to such items has been deleted.  As a result,
continuity may be compromised.  

TechNotes is a monthly publication from the Ashton-Tate Software Support
Center.  For subscription information, call 800-545-9364.

Dates to Remember
John Grant

There seems to be a degree of confusion in understanding how dBASE IV
appears to handle date data types.  As with most things in life, there are
exceptions to the rule.  Where a date calculation may be correct
syntactically, there are cases where a variance will make the average dBASE
date function produce incorrect results.  So the purpose of this article is
to round out the roster of dBASE date algorithms.  It is important to
understand how dBASE IV 1.1 deals with dates.  Below are some examples that
demonstrate dBASE IV date manipulation and some of the unique features.  

Invalid Dates

dBASE IV 1.1 will accept invalid dates and make them valid.  Note that 13
is an invalid month but the converted date appears to be intuitively
correct.

    .? {13/02/90}
    01/02/91

In this example the month is incorrect and the 29th day of February is also
incorrect but again the converted date appears to be correct.

    .? {14/29/90}
    03/01/91

Also note that the character to date operators {} and CTOD work slightly
different.  The date operator,{},is sensitive to miscellaneous spaces.

    .? {01/....01/90} && 4 spaces
    01/01/09

    .? {01/.....01/90}  && 5 spaces
       /  /

The dBASE IV function CTOD appears to be tolerant of miscellaneous spaces. 
However, it is a good idea to TRIM or restrict the length of each date
component when concatenating them together.

    .? CTOD("01/.....01/90")  && 5 spaces
    01/01/90

    .? CTOD("01/....................01/90") && 20 spaces
    01/01/90

You can restrict the length of the month component with the STR function.

    ? CTOD(STR(MONTH(DATE()),2)+"/01/90")
    12/01/90

Days

The addition and subtraction of days is straight forward.  Simply add or
subtract the number of days to a date.  

    .? {01/01/90}+500
    05/16/91

    .? {01/01/90}-500
    08/19/88

However if you add the days directly to the day portion of the date the
largest number supported is 32767.

    .?{01/32767/1981}
    09/16/2008

After increasing the day component of the date to 32768 the following
example is incorrect.
    .?{01/32768/1981}
    04/13/1829

Months

Months, unlike days, cannot be added directly to the date such as DATE() +
x(months) and 31*3 is not always 3 months worth of days.  The only way to
add months to a date is to add them to the month component of the date. 
This will require you to fragment the date into each component (month, day
and year), and then put them back together.

The month component of the date cannot exceed 14 months.  Hence the ability
to add months will work as long as you do not create a month larger than
14.  You can create a loop to add one month at a time.  This method will
work until you reach the date limits supported by dBASE IV.

    .? {14/01/90}
    02/01/91

    .? {14/28/90}
    02/28/91

As you can see, 14/01/90 yields 02/01/91 and 14/28/90 yields 02/28/91 which
is intuitively correct.  However, the next example is not correct.

    .? {15/01/90}
    03/03/1991

The following UDF will create valid dates by adding one month at a time.

FUNCTION: AddMonth

FUNCTION AddMonth
    PARAMETERS adate,nmonth
    
    * adate is the base date you wish to add to
    * nmonth is the number to add
    
    DO WHILE nmonth <> 0
      adate = CTOD(STR(MONTH(adate) + IIF(nmonth < 0, -1, 1), 2) ;
        + "/" + STR(DAY(adate), 2) ;
        + "/" + STR(YEAR(adate), 4))
      nmonth = nmonth + IIF(nmonth < 0, 1, -1) 
    ENDDO
RETURN adate

Years

Years, like months, permit addition and subtraction of whole numbers from
the year component of a date.  Unlike months however, the largest year  can
be greater than 14; the only limitation is the dBASE IV date limits.

Difference

The difference between two dates can be calculated by using the arithmetic
operator minus (-).  This is very easy to use but will only return the
number of days and not the number of months or years.  For example: the
function YearDif(xdate, ydate) or MonthDif(xdate, ydate) would be
favorable.  The following examples will demonstrate how you might find the
number of years or months.  They calculate whole years and months, not the
fractional portions, for example: 02/15/90 - 01/01/90 = 1 month not 1.5,
the result being rounded down.

Function: YearDif

FUNCTION YearDif
    PARAMETERS xdate,ydate
    PRIVATE years
    years = YEAR(xdate) - YEAR(ydate) + ;
      IIF(MONTH(xdate) < MONTH(ydate), -1, ;
      IIF(MONTH(xdate) > MONTH(ydate), 0,  ;
      IIF(DAY(xdate) < DAY(ydate), -1, 0)))
RETURN years

The difference in years between two dates can be derived from the number of
days multiplied by the constant 364.14, which incorporates the leap years. 
So the function YEARDIF could be simplified to the following:

    years = ABS(xdate - ydate) / 364.14

The routine to calculate the number of months can be derived from the
number of years.

Function: MonthDif

FUNCTION MonthDif
    PARAMETERS xdate, ydate
    PRIVATE xmonths
      xmonths = (ABS(YEAR(xdate)  - YEAR(ydate)) +;
        IIF(YEAR(xdate) = YEAR(ydate), 0, -1)) * 12+;
        IIF(YEAR(xdate) = YEAR(ydate), ;
        ABS(MONTH(xdate)  - MONTH(ydate)),;
        (12-MONTH(xdate) + MONTH(ydate)))
RETURN xmonths

As you can see the dates are accepted as invalid dates and transformed to
valid, but not necessarily correct dates.  The question arises, how can you
use these features to your advantage?  Below are some examples of how you
might use these features:

Last day of the month given any day in that month.

    .? DATE()
    08/12/90

    . ? (DATE() +(32 -DAY(DATE()))) -DAY(DATE() +(32 -DAY(DATE())))
    08/31/90

The first day of the month given any day in that month.

    . ? DATE()  - DAY(DATE()) + 1
    08/01/90

The first day of any month is always 01, but the example demonstrates the
ability to subtract days from a date and produce the correct date.  For
example, the date of the previous Friday given any date.

    . ? DATE()
    09/17/90

    . ? CDOW(DATE() - DOW(DATE()) -1)
    Friday

    .? DATE() - DOW(DATE())  - 1
    09/14/90

Every Sunday in a month for December 1990.

    . ? DAY((DATE() -DAY(DATE()) +1) +(8 -DOW(DATE() -DAY(DATE()) +1)))
    2
    . ? DAY((DATE() -DAY(DATE()) +1) +(8 -DOW(DATE() -DAY(DATE()) +1)) +7)
    9
    . ? DAY((DATE() -DAY(DATE()) +1) +(8 -DOW(DATE() -DAY(DATE()) +1)) +14)
    16
    . ? DAY((DATE() -DAY(DATE()) +1) +(8 -DOW(DATE() -DAY(DATE()) +1)) +21)
    23
    . ? DAY((DATE() -DAY(DATE()) +1) +(8 -DOW(DATE() -DAY(DATE()) +1)) +28)
    30

The constant "8" is used to find Sundays, but this constant could be
adjusted to find any day in the week.  Also, note how the syntax is the
same for all but the first Sunday.

So there it is.  One developer's point of view on a commonly used but often
misunderstood corner of the dBASE world. 

