CTOT(), DTOT(), TToC(), TToD()

These functions convert between dates and characters, and datetimes. Like their predecessor functions DTOC() and CTOD(), each can be read as “something TO something.” For example, DTOT() can be understood if said as “Date TO dateTime.” CTOT() converts a Character string TO a dateTime.

Usage

tReturnValue = CTOT( cString )
tReturnValue = DTOT( dDate )
cReturnValue = TTOC( tDateTime [ , nFormat ] )
dReturnValue = TTOD( tDateTime )

CTOT() is pretty smart. If anything less than a full time string is specified, it does its best to figure out what you mean, but it does need some help from you. In particular, in VFP 3, CTOT() is unable to parse the time segment without at least one colon. This was fixed in VFP 5. Here is what it does under a variety of conditions:

? CTOT("7/4/1976")             && 07-04-1976 12:00:00 AM
? CTOT("7/4/1976 ")            && 07-04-1976 12:00:00 AM
? CTOT("7/4/1976 1")           && In VFP 3, empty datetime variable
                               && 07-04-1976 01:00:00 AM in VFP 5 and later
? CTOT("7/4/1976 12")          && In VFP 3, empty datetime variable
                               && 07-04-1976 12:00:00 PM in VFP 5 and later
? CTOT("7/4/1976 12:")         && 07-04-1976 12:00:00 PM
? CTOT("7/4/1976 12:1")        && 07-04-1976 12:01:00 PM
? CTOT("7/4/1976 12:11")       && 07-04-1976 12:11:00 PM
? CTOT("7/4/1976 12:11:")      && 07-04-1976 12:11:00 PM
? CTOT("7/4/1976 12:11:1")     && 07-04-1976 12:11:01 PM
? CTOT("7/4/1976 12:11:11")    && 07-04-1976 12:11:11 PM

VFP 7 adds the capability to convert XML dates, in the format “yyyy-dd-mmThh:mm:ss”.

VFP 7 does indeed add the ability to convert XML dates; however, you must first issue: SET DATE YMD Then you can try the following: ? CTOT("1976-07-04T12:11:11") && 07-04-1976 12:11:11 PM Microsoft says that this isn't a bug; it's an omission in the documentation. We think you shouldn't have to SET DATE before using a standard date format. And if this info is missing from the docs, then it's a bug in the docs. It's a bug. And it's another reason to keep up with the Service Packs; VFP 7 SP1 fixes this problem.

This one's really a bug, although a short-lived bug. It was introduced in VFP 7 and fixed in SP1. In the original VFP 7 version, the XML date format allows fractional seconds, and the Help file says fractional seconds can be used. In fact, the Help file even gives an example. But try this (don't forget to SET DATE YMD first): ? CTOT("1976-07-04T12:11:11.555") && Empty! Seems to us that there's something of value there that can be parsed, and an empty date should not be returned. Fortunately, it's fixed in SP1.

DTOT() converts date to datetime values by setting the datetime variable to the same date and setting the time to midnight. TToC() returns a character string, which respects the setting of SET CENTURY, SET SECONDS and SET HOUR, so your return value can vary from 14 to 21 characters in length. TToD() converts a datetime to a date by truncating the time portion.

The two functions for converting characters to date and datetimes are a potential source of ambiguous dates. Because the interpretation of the string takes place at runtime, the settings in place at that time for DATE and CENTURY can affect the interpretation of the string. If a routine is called from more than one place, it is possible for the same line of code to yield two different date values. For these reasons, SET STRICTDATE has a special setting (2) just for detecting the CTOD() and CTOT() functions in code, and flagging them as places where the potential for ambiguous dates exists. See the section on SET STRICTDATE for details.

TTOC takes one optional parameter, starting in VFP 7. This parameter changes the format of what’s returned. Omitting the parameter entirely returns the standard character string. A value of 2 returns only the time portion of the string. According to the documentation, a value of 1 returns a numeric string suitable for indexing (see the example below). In fact, passing any value other than 2 returns this string.

Example

SET STRICTDATE TO 0
SET DATE TO MDY
? DTOT({^1995-04-01})
* Returns 04/01/1995 12:00:00am

? TTOD({^1995-04-01 12:34:56p})
* Returns 04/01/1995

? CTOT( "9/28/1958 9:02" )
* Triggers warning if StrictDate = 2
* Otherwise returns {09/28/58 09:02:00 AM}

? TTOC({04/01/1995 12:00:00am})
* Returns "04/01/1995 12:00:00am"

? TTOC({04/01/1995 12:00:00am}, 1)
* Returns "19950104000000"

? TTOC({04/01/1995 12:00:00am}, 2)
* Returns 12:00:00 AM

There’s one conversion many folks feel is missing. There’s no function to convert a date and a time to a new datetime variable (for example, the date and time in the third and fourth columns of an array filled by ADIR()). There are two possibilities, both pretty simple. If the time is in the format returned by SECONDS(), as the number of seconds since midnight, just add the numeric seconds since midnight to the converted date-to-datetime.

tNewDateTime = DTOT(dOldDate) + nTimeInSeconds

On the other hand, if you’ve stored the time in an Hour:Minute:Second character string format, the following expression should take care of your needs:

tNewDateTime = CTOT(DTOC(dOldDate) + ' ' + cTimeInSeconds)

See Also

ADir(), CtoD(), Date(), DateTime(), Day(), DoW(), DMY(), DtoC(), GoMonth(), MDY(), Month(), Set Century, Set Date, Set Hours, Set Mark To, Set Seconds, Set StrictDate, Year()