For FoxPr 2.6 fans wondering whether to jump to Visual Fox, here's one reason: you can handle time without any fancy programming.

That's because Visual Fox includes a date time data type. It's separate from the old date data type, which remains in the newer versions. And along with the new category come some handy functions. Here's an overview with tips to get things done faster and avoid mistakes.

Converting your data

Let's assume you want to convert data stored as character. You can create a new date time field by taking your mouse to Table Properties and modifying the structure. After inserting a new column, go to data type and pick date time. That's a choice you didn't have in earlier versions of Fox. Now go back to your command window. To bring in data from a character column, you'll need to use the character-to-time function ctot(). Let's look at how it works.

Let's say you're starting with three separate character columns, one with dates, another with times, and a third designating a.m. or p.m. Pretend this is part of a database showing the times that city officials made telephone calls:

CALLDATE CALLTIME AMorPM 12/31/95 12:45 AM 09/14/60 7:35 PM 01/10/60 7:40 AM 10/8/96 11:35 AM

(Notice the data is a little inconsistent. Sometimes you've got a leading zero in your day or time. Sometimes you don't. It doesn't matter - Fox will cut you slack. You also won't need to trim any leading spaces.) In this example, we'll pretend you've created a new date time column called CALLTIME.

Now we're ready to use the ctot function. Have no fear. It should be familiar territory to anyone who's messed with Fox functions. To fill in the times in the new column, run this command:

Replace all CALLTIME with ; ctot(CALLDATE + CALLTIME + AMorPM)

The result should look something like this:

12/31/95 12:45:00 AM 09/14/96 07:35:00 PM 01/10/97 07:40:00 AM 10/08/96 11:35:00 PM

And you're done converting your data.

Missing or bizarre entries

If, for some reason your data includes a time that cannot exist, like 25:00, the replacement for that record won't work. Nothing will be inserted. The same goes for an invalid date, even if you have a valid time. Nothing will be inserted. However, if you have a valid date and nothing in your time field, Fox will insert the date plus 00:00:00, which is midnight. And, if you have a valid time but no date, Fox will insert 12/30/1899. (If you forgot how to see what century it is, simply type in your command window: SET CENTURY ON.) If these seem like boring details, keep in mind that this can mean the difference between a query that gets a right or wrong answer.

How do I look?

The default setting shows time with seconds. If you don't like this - your data doesn't have seconds and you don't like looking at all those zeros - type SET SECONDS OFF in your command window. Away go all those pesky :00s.

In addition, you may find it easier to ask some questions by using military time. That's where the clock goes all the way to 24:00 and you no longer need a.m. or p.m. To do that, type SET HOURS TO 24. Now everything will be in military time. Then again, if you hate seeing things in military time, the opposite command is SET HOURS TO 12.

A query sampler

Let's say you want to find all calls after 2 p.m. on Sept. 12, 1996, when a big piece of legislation was introduced:

Select * ; From PhoneCalls ; Where CALLTIME > { 9/14/96 14:00}

Here you need to use the curly braces, the same way you needed them for the original date data type. Let's say you want to find all calls between p.m. on Sept. 12, 1996, and 10 a.m. on Jan. 15, 1997, when the big piece of legislation finally came up for a vote:

Select * ; From PhoneCalls ; Where between (CALLTIME, {9/12/96 14:00}, {1/15/97 10:00}

Again, you need to use the curly braces. Now let's say you want to find out who's so important that the mayor is calling after 10:00 at night. If you have things set to military time, the query is simply:

Select * ; From PhoneCalls ; Where hour(CALLTIME) > 22

Notice that when you use the hour() function, the criteria is numeric. You don't need any punctuation, like quotes or curly braces.

Time differences

Let's say you have the starting time of a phone call and the ending time, and you want to know how long a city council member called that 900 number. Remember that the date time data type answers with the number of seconds - even if you've set seconds off. So if you want your answer in seconds, don't do a thing. If you want it in minutes, divide by 60. In hours, by 3600. And so on. For example:

Select , (ENDCALL - BEGINCALL) / 60 as CallLength ; From PhoneCalls

This will tell you the call's length, in minutes. Notice that you need to put the EndCall - BeginCall in parentheses. The reason that must be done is to first generate the difference a result that will be numeric and can then be divided by 60.

Let's say that, for some reason, you have one column as a date field, and another as a date time field and you want to find the difference between them. Let's say it's an arrest database. The date field is Birthday, and the date time field is Arrested, noting when someone was busted. For this mixing and matching, you can use the ttod() function. Here's an example. (This time, dividing converts our answer into years, to see how old they were when arrested):

Select (ttod(Arrested) - Birthday) /365.25 as ArrestAge ; From Crimes

Catching nulls

Remember in the section on missing and bizarre entries? Let's say you want to find an instance when no data is in the field. There are two ways to do that: with the isblank or empty() functions. The isnull() function will not work because Fox has yet to allow true null designations for these cases. So here's an example to catch them:

Select * ; From PhoneCalls; Where empty(EndCall)

And, since you know what Fox inserts when you have a valid time but no date, or vice versa, you can run filters to catch or ignore those entries.

Watch out! A caution about averages

Remember how Fox will convert proper days with no time (by adding midnight) or no days with proper time (by assuming the day is 12/30/1899)? Well, don't forget that can screw up any summary calculations. For some reason, Fox will consider empty entries when running averages unless you tell it otherwise. Regardless, be careful to filter for these kinds of values if you don't want to include them.

Andy Lehren can be reached by e-mail at alehren@nbc.com