Computer Dating with Visual FoxPro

Jim Booth

The year 2000 sends shivers down the spine of many a database developer. Visual FoxPro developers need not worry about the turn of the century because of the date support in the product. This month, Jim examines the commands that provide the necessary support for the next millennium and looks into some valuable date manipulation routines.

Two's company and four's a crowd

Often heard is the bellowing cry, "I want to use only two digits for the year in my dates." Prior to VFP version 5.0, allowing the user to do this presented problems when dealing with dates after 12/31/1999. Many solutions have been suggested, but they've all suffered from one weakness -- the inability to handle the date February 29, 2000. In versions prior to 5.0, FoxPro would interpret the date as 02/29/1900 and cause an invalid date error because there was no February 29 in 1900, although there will be one in 2000. The trouble is that this error was generated before the Valid event was fired, and thus the Valid()'s attempt to adjust for the special case of the year 2000 will fail. (Christof Lange has developed one solution to this problem that works in versions of FoxPro prior to 5.0. The solution will be placed in the public domain this spring.)

If you're using VFP 5.0 or later, this problem doesn't exist because we have the SET CENTURY TO nCentury ROLLOVER nYear command. This command allows you to define a year at which dates earlier than that year are assumed to be the century following the century specified, while dates after the year are assumed to be the century specified. Go to the Command window and execute the following code:

SET CENTURY ON
SET CENTURY TO 19 ROLLOVER 78
? {12/12/98}
12/12/1998 
? {12/12/70}
12/12/2070

So far, so good. Now set the SET("CENTURY") setting back to the default:

SET CENTURY TO

Next, write a program using the following:

* setcent1.prg
* Demonstration of the SET CENTURY syntax in VFP 5.0

? {02/26/00}
? {02/26/92}

* We will set the century to 1900 with a rollover for 
* any year lower than 70.

SET CENTURY TO 19 ROLLOVER 70 OFF

* Turn century on so we can see what century the 
* dates have
SET CENTURY ON

? {02/26/00}
? {02/26/92}

Run the program, and take a look at the results. Whoa! The screen says 02/26/1900 and 02/26/1992. But the century was set to 19 with a rollover at 70, so shouldn't the dates display as 02/26/2000 and 02/26/1992? This is a situation that's burned many a developer. Those dates in the program code are converted to date data at the time the program is compiled. The settings at compile time -- not when the program is run -- affect the century that the dates are assigned. When the program was compiled, the SET CENTURY TO 19 line had not been executed yet. The default settings were used to compile the dates. Even if we run the program again, we'll see 19 used as the century for both dates. This is because the program is already compiled and those dates are hard-coded into the program (literals). To get what we want, we must compile the program after the SET CENTURY TO has been executed. Typing the SET CENTURY command in the Command window and then recompiling the program provides the expected results.

This compile-time assignment of dates makes for some very interesting problems. For example, the following code will display a blank date if the SET DATE setting is AMERICAN in your development environment:

* setcent2.prg

SET DATE BRITISH

? {25/11/1998}

SET DATE AMERICAN

The reason you'll get the blank date is that the SET DATE of AMERICAN will control the interpretation of the date literal {25/11/1998}, and there is no month 25. The SET DATE line won't execute until you run the code, and by that time the date literal has already been compiled. The cardinal rule, then, is to use caution when putting date literals in your code.

So how old are you, anyway?

How many times have you needed to compute an age from two dates? Probably fairly often if you work with people in your tables. At first glance, this seems like an easy process -- just subtract the earlier date from the later one and divide by 365 (days in a year). However, due to leap years, that algorithm will get you close but not exact. In order to compensate for the extra day every four years, you could use 365.25 as the divisor. Yet, again, the results will be close but not exactly correct for all possible dates. The reason is that every year that's evenly divisible by 100 isn't a leap year -- only those that are evenly divisible by 400 are leap years. (For example, 1900 isn't a leap year, but 2000 is. Who made up these rules, anyway?)

Here I've taken a completely different approach and calculated the time between the dates. This way, I can control how accurate the result is by how the code is written. The following code is an example of a program that will calculate the time between two dates:

* HowOld.prg

* Takes two dates or datetimes and returns difference
* as a character string of nn Years and nn Months 
* returns NULL for invalid dates

LPARAMETERS pdDate1, pdDate2

* Declare working variables
LOCAL lnYears, lnMonths, ldDate1, ldDate2, ;
      LcType, lcReturn

* Set the data type of lcReturn to Character
lcReturn = ""
* Now set its value to .NULL.
lcReturn = .NULL.

* Check the data type for the first parameter
lcType = TYPE("pdDate1")
IF NOT lcType $ "DT"
  * Illegal type for date
  RETURN lcReturn
ENDIF

* Check the second parameter
lcType = TYPE("pdDate2")
IF NOT lcType $ "DT"
  * Illegal type for date
  RETURN lcReturn
ENDIF

* Get the latest date
ldDate1 = MAX(pdDate1,pdDate2)

* Get the earliest date
ldDate2 = MIN(pdDate1,pdDate2)

* Now figure out the years and months
* Start with 0 years, 0 months
lnYears = 0
lnMonths = 0

* Move forward from ldDate2 by 1 month
ldDate2 = GOMONTH(ldDate2,1)

* Loop as long as ldDate1 is later than ldDate2
DO WHILE ldDate2 < ldDate1
  * Add one to lnMonths
  lnMonths = lnMonths + 1
  * Check for a year passing
  IF lnMonths = 12
    * Increment the years
    lnYears = lnYears + 1
    * Zero the months
    lnMonths = 0
  ENDIF
  * Increment the earlier date
  ldDate2 = GOMONTH(ldDate2,1)
ENDDO

* Build the return string
IF lnYears > 0
  * Put in the number of years
  lcReturn = ALLTRIM(STR(lnYears)) + " Year"
  IF lnYears > 1
    * If years is over 1 add an s to Year
    lcReturn = lcReturn + "s"
  ENDIF
ENDIF

* Add one the number of months
lcReturn = lcReturn + " and " + ALLTRIM(STR(lnMonths)) ;
           + " Month "
IF lnMonths <> 1
  * If months is NOT 1 add an s to month
  lcReturn = lcReturn + "s"
ENDIF 

* Return the string
RETURN lcReturn

The preceding code is commented to clearly describe what's going on. The difference between this approach and the more common one is that here we're counting the months rather than using the number of days. This eliminates the problem of the number of days in a year causing rounding errors. This approach also eliminates the problem of DateTime values, where the subtraction returns the number of seconds and not the number of days. Try calling HowOld and passing a DateTime value to it. (Remember to watch the SET CENTURY setting, or the year you pass might be interpreted as being in a different century than the one you were expecting!)

Next month, I'll take a look at the TableUpdate function in VFP 5.0. It has some improvements from the 3.0 version that you're going to like.

Download sample code for this article here.

Jim Booth is a Visual FoxPro developer and trainer. He has spoken at FoxPro conferences in North America and Europe. Jim has been a recipient of the Microsoft Most Valuable Professional Award every year since it was first presented in 1993. 203-758-6942, 72130.2570@compuserve.com.