Take care with dates and times

Don't let date and datetime anomalies catch you out.

Visual FoxPro's array of date and datetime functions normally work flawlessly ... but the results are not always what you might expect. For instance, assigning a "blank" date value to a variable, like this:

a = {0/0/0}

will correctly create a date variable. But assigning a blank datetime does not produce a datetime variable. Thus:

b = {0/0/0 0:00AM}

makes b a date, not a datetime as you might have thought.

Curiously, the following code does create an empty datetime:

b = {/:}

Another unexpected behavior is that this construct:

{:}

translates to:

12/30/1899 12:00:00AM

Migration

If you are migrating to VFP from FoxPro 2.x, be aware that the former is stricter than the latter in its use of date separators. For example, CTOD("1*12*98") is valid in 2.x, regardless of SET SEPARATOR, but it generates an invalid date in Visual FoxPro.

Year 2000 issues

Visual FoxPro is, for the most part, Year 2000 compliant ... but care is needed. To demonstrate, let's look ahead ten years from the day this article was written:

SET CENTURY ON
dAnniv = {12/30/07} && ten years from 12/30/97

Now subtract ten years from that date, like this:

? GOMONTH(dAnniv,-120)

If you are using VFP 5 or earlier, the result will be 12/30/1897 – a hundred years earlier than you might have expected.

That's not a bug. The point is that, when you specify a two-digit year (in VFP 5 or earlier), the century digits are always taken as 19 – even if CENTURY is on, and regardless of the system date. If, during 1998 and 1999, your users will be entering forward dates for the next century – contract expiration dates, for instance – you might want to amend your forms to allow the full four-digit year to be entered.

By putting the following line of code at the start of your application:

SET CENTURY TO 20

you ensure that the century digits in a two-digit year are always 20.

Going further, this line of code:

SET CENTURY TO 19 ROLLOVER 45

says that the century digits in a two-digit year will be 19 if the last two digits are 45 or higher, otherwise the century digits will be 20. In many cases, adding this code will be the only action you need to take to allow your users to go on entering just the last two digits of the year.

By the way, these two forms of SET CENTURY were new to VFP 5.0. They won't work with earlier versions.

In VFP 6.0, the rollover is established by default, with the pivot year set to the current year digits plus 50. So, in 1999, a two-digit year will be interpreted as being in the range 1950 to 2049. Then, in each following year, the range will move forward one year.

Text box bug

If you use the DATE() function in a text box, and if the system date is 2000 or later, you will always see a four-digit year, regardless of SET CENTURY. This is a bug, which appears to be present in all versions of VFP. There is a simple workaround: set the InputMask property to 99/99/99.

"Give us back our eleven days"

You probably know that GOMONTH() can be used to add or subtract a number of months to or from a date. Thus:

GOMONTH({1/1/1990},-12)

returns 1/1/1980.

But try this: 

GOMONTH({12/31/1752},-12)

The result is an invalid date. In fact, GOMONTH() fails for any date before September 14, 1753.

What is the significance of that date? There might be no connection, but it was on September 2, 1752 that England (along with Great Britain's American colonies) finally got around to adopting the Gregorian, or New Style, calendar. To bring the country into line, an 11-day correction was needed. So September 2 was immediately followed by September 14.

We don't know whether this in any way explains the quirk in GOMONTH(). But other Visual FoxPro date functions seem unaware of the date change. For example,

CDOW({9/14/1752}) 

correctly returns Thursday. But

CDOW({9/2/1752})

incorrectly returns Saturday –– it was in fact a Wednesday (at least it was in England).

Earliest date

Still in a historical mood, what do you suppose is the earliest date that VFP can handle? Not {0/0/00}, which is considered to be an empty date. What about {1/1/00}?. Pass that date to CDOW(), for example, and you will see that it was a Monday. But think about it. {1/1/00} is not January 1st in the year zero. In VFP 5.0 or earlier, it is the first day of 1900. Remember, two-digit years default to 19xx. Similarly, in VFP 6.0, it is taken as 1/1/2000, which is a Saturday.

In fact, there never was a year zero. In both the modern Gregorian calendar and its immediate ancestor, the Julian calendar, 1 BC was immediately followed by 1 AD. Perhaps the omission of a zero year was an oversight on the part of Sosigenes, the Greek astronomer who devised the Julian calendar. But it's a fact. And it is at the root of the recurring argument about whether the third millennium really starts in 2000, or in 2001 as many people assert.

Mike Lewis Consultants Ltd. January 1998.