Back

Originally published in FoxTALK April 1998


Computer Dating with Visual FoxPro

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 we will examine the commands that provide the next millennium support as well as look 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 Visual FoxPro version 5.0 this presented problems when dealing with the turn of the century.  There have been many suggested solutions that suffered from one weakness, that is the handling of the date February 29, 2000.  You see, if you entered a two digit year then the Fox would interpret the date as 02/29/1900 and cause an invalid date error before the Valid event could adjust for the year 2000.  There was no February 29 in the year 1900 but there will be one in 2000.  Christof Lange developed one solution that handled this problem that works in version of FoxPro prior to 5.0.  The solution is being described in another journal and will be placed in the public domain.

If you are using Visual FoxPro 5.0 or later then this problem doesn’t exist because we have the SET CENTURY TO nCentury ROLLOVER nYear command.  The code below demonstrates the use of this command.

* Demonstration of the SET CENTURY syntax in VFP 5.0

* 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}

So now we run the program and we see …, uh oh the screen says 02/26/1900 and 02/26/1992.  But we set the century to 19 with a rollover at 70, so shouldn’t we see the dates as 02/26/2000 and 02/26/1992?  This is a situation that has burned many a developer.  You see those dates in the program code are converted to date data at the time the program is compiled.  The settings at compile time affect the century that the dates get.  So when the program was compiled the SET CENTURY TO line had not been executed yet.  The default settings were used to compile the dates.  Even if we run the program again we will see 19 used as the century for both dates.  This is because the program is already compiled and those dates are hard coded (literals) into the program.  To get what we want we need to 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 gets us the results we expected.

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.

SET DATE BRITISH

? {25/11/1998}

The reason you will 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 will not execute until you run the code and by that time the date literal has already been compiled.

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 form the later date and divide by 365 (days in a year).  That algorithm will get you close but not exact.  Then use 365.25 as the divisor.  Still it gets us close but not exactly the right age for all possible dates.

We could take a completely different approach and try counting the time between the dates.  This way we can control how accurate our result is by how we write the code that does it.  The code shown below 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 code above is commented to clearly describe what is going on.  The difference between this approach and the more common one is that here we are 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.