Date Functions

Functions included in this section are listed below. Click a link to jump to that function.

Two data types contain dates: Date and DateTime. The Date data type stores dates without time values. DateTime stores values that are dates, times, or both. Strict Date and DateTime formats do provide for year 2000 compliance. It is recommended that you use Strict Date formats for all Date constants and expressions. A Strict Date always evaluates to the same Date or DateTime value regardless of any date settings.

Date constants can be formatted as {mm/dd/yy} or {mm/dd/yyyy} or in Strict Date format {^yyyy-mm-dd[,][hh[:mm[:ss]][a|p]]}. Time is formatted {hh:mm[:ss][a|p]}.

The following rules apply for both Date and DateTime data types:

CDOW( ) Function
CMONTH( ) Function
CTOD( ) Function
CTOT( ) Function
DATE( ) Function
DATETIME( ) Function
DAY( ) Function
DMY( ) Function
DOW( ) Function
DTOC( ) Function
DTOS( ) Function
DTOT( ) Function
GOMONTH( ) Function
HOUR( ) Function
MAX( ) Function
MDY( ) Function
MIN( ) Function
MINUTE( ) Function
MONTH( ) Function
SEC( ) Function
SECONDS( ) Function
TIME( ) Function
TTOC( ) Function
TTOD( ) Function
WEEK( ) Function
YEAR( ) Function

CDOW( ) Function

Purpose Returns the day of the week from a given Date or DateTime expression.
Syntax CDOW(<expD>)
Returns Character (returns from a Date expression the name of the day of the week. For example: Sunday or Monday.)
Parameter <expD> The Date or DateTime expression <expD> for which CDOW( ) returns the day.
Remarks CDOW( ) returns the name of the day of the week as a string in proper noun format.
Examples
CDOW({10/5/1998}) Returns: Monday
CDOW({^1999-09-09}) Returns: Thursday

CMONTH( ) Function

Purpose Returns the name of the month from a given Date or DateTime expression.
Syntax CMONTH(<expD>)
Returns Character
Parameter <expD> The Date or DateTime expression <expD> can be a function that returns a date, a date type memory variable, array element or field.
Remarks Returns the name of the month as a string in proper noun format
Examples
CMONTH({9/25/92}) Returns: September
CMONTH({^1995-03-05}) Returns: March

CTOD( ) Function

Note: Applied Systems, Inc. recommends the use of the TAM functions EIGHTTOD() and SIXTOD() instead of CTOD() for all TAM date fields.

Purpose Converts a character expression to a Date expression.
Syntax CTOD(<expC>)
Returns Date
Parameter <expC> The character expression <expC> is converted to a date value by CTOD( ). <expC> must contain a valid date from 1/1/100 to 12/31/9999.

The default format for <expC> is mm/dd/yy. You can use SET DATE and SET CENTURY to change the default format. If the century isn't specified when entering a date (as in the character expression 12/25/91), the twentieth century is assumed.
Examples CTOD(‘7/4/1976’)     Returns: 07/04/76

CTOT( ) Function

Purpose Returns a DateTime value from a character expression
Syntax CTOT(<expC>)
Returns DateTime
Parameter <expC> Specifies the character expression from which a DateTime value is returned.
Remarks Note that CTOT( ) can create ambiguous DateTime values.
Examples CTOT(’12:00AM’) Returns: 12:00:00AM

DATE( ) Function

Purpose Returns the current system date, which is controlled by the operating system, or creates a year 2000-compliant Date value.
Syntax DATE([<expN1>, <expN2>, <expN3>])
Returns Date
Parameter <expN1> Specifies the year returned in the year 2000-compliant Date value. <expN1> can be a value from 100 to 9999.

<expN2> Specifies the month returned in the year 2000-compliant Date value. <expN2> can be a value from 1 to 12.

<expN3> Specifies the day returned in the year 2000-compliant Date value. <expN3> can be a value from 1 to 31.
Remarks DATE( ) returns the current system date if it is issued without the optional arguments. Include the optional arguments to return a year 2000-compliant Date value.
Examples
DATE () Returns: system date
DATE(1999,06,12) Returns: 06/12/1999

DATETIME( ) Function

Purpose Returns the current Date and time as a DateTime value, or creates a year 2000-compliant DateTime value.
Syntax DATETIME([<expN1>, <expN2>, <expN3> [,<expN4> [,<expN5>,[<expN6>]]]])
Returns DateTime
Parameter <expN1> Specifies the year returned in the year 2000-compliant Date value. <expN1> can be a value from 100 to 9999.

<expN2> Specifies the month returned in the year 2000-compliant Date value. <expN2> can be a value from 1 to 12.

<expN3> Specifies the day returned in the year 2000-compliant Date value. <expN3> can be a value from 1 to 31.

<expN4> Specifies the hours returned in the year 2000-compliant DateTime value. <expN4> can be a value from 0 (midnight) to 23 (11 P.M). Defaults to 0 if omitted.

<expN5> Specifies the minutes returned in the year 2000-compliant DateTime value. <expN5> can be a value from 0 to 59. Defaults to 0 if omitted.

<expN6> Specifies the seconds returned in the year 2000-compliant DateTime value. <expN6> can be a value from 0 to 59. Defaults to 0 if omitted.
Remarks DATETIME( ) returns the current system DateTime if it is issued without the optional arguments. Include the optional arguments to return a year 2000-compliant DateTime value.
Examples
DATETIME() Returns: system date and time
DATETIME(1999,6,12) Returns: 06/12/1999
DATETIME(1988,4,29,3,57) Returns: 04/29/1988 3:37:00AM

DAY( ) Function

Purpose Returns the numeric day of the month for a given Date or DateTime expression
Syntax DAY(<expD>)
Returns Numeric Returns a number from 1 through 31.
Parameter <expD> The Date or DateTime expression <expD> can be a date literal, a date type memory variable, an array element or a field.
Examples
DAY(DATE()) Returns: day of the current month
DAY({3/5/1999}) Returns: 5
DAY({^1999-10-31}) Returns: 31

DMY( ) Function

Purpose Returns a specified Date or DateTime expression in Day Month Year format.
Syntax DMY(<expD>)
Returns Character
Parameter <expD> (The Date expression you want to return in Day Month Year format.)
Remarks (Returns a character string in a Day Month Year format (for example, 31 May 1965) from a Date expression. The month name isn't abbreviated.

If SET CENTURY is OFF, DMY( ) returns a character string in a DD Month YY format (for example, 31 May 65). If SET CENTURY is ON, the format is DD Month YYYY (for example, 31 May 1965).
Examples
DMY(DATE()) Returns: 02 September 1999
DMY({^1999-4-19}) Returns: 19 April 1999

DOW( ) Function

Purpose Returns the day of the week from a given Date or DateTime expression.
Syntax DOW(<expD>)
Returns Numeric
Parameter <expD> Specifies the day number for the Date or DateTime expression <expD>.
Remarks The value returned by DOW( ) ranges from 1 (Sunday) through 7 (Saturday).
Examples
DOW({6/6/92}) Returns: 7
DOW({^1988-11-27}) Returns: 1

DTOC( ) Function

Purpose Returns a character type date from a Date or DateTime expression.
Syntax DTOC(<expD>[, 1])
Returns Character
Parameter <expD> can be a date type memory variable, an array element, the name of a field, or a function that returns a character-type date.

If the optional argument 1 is included, the date is returned in a format suitable for indexing (YYYYMMDD). This is particularly useful in combination with TIME( ) for maintaining the table/.DBF records in chronological sequence.
Remarks Returns a character string corresponding to the Date expression <expD>. The date format is determined by SET CENTURY and SET DATE.
Examples
DTOC({12/2/1990}) Returns: ‘12/02/1990’
DTOC({^1997-8-20}) Returns: ‘08/20/1997’
DTOC({2/7/92},1) Returns: ‘19920207’

DTOS( ) Function

Purpose Returns a character-string date in the format YYYYMMDD from a specified Date or DateTime expression.
Syntax DTOS(<expD>)
Returns Character
Parameter <expD> is the Date expression you want to covert to an eight-digit character string.
Remarks This function is useful for indexing tables/.DBFs on a date field. It is equivalent to DTOC( ) when its optional 1 argument is included.

The character string returned by DTOS( ) isn't affected by SET DATE or SET CENTURY.
Examples
DTOS(DATE()) Returns: 19990902
DTOS({5/21/98}) Returns: 19980521

DTOT( ) Function

Purpose Returns a DateTime value from a Date expression.
Syntax DTOT(<expD>)
Returns DateTime
Parameter <expD>) Specifies the Date expression from which a DateTime value is returned.
Remarks The format of the DateTime value DTOT( ) returns depends on the current SET DATE and SET MARK settings. If a century isn't supplied, the twentieth century is assumed. DTOT( ) adds a default time of midnight (12:00:00 A.M.) to the date to produce a valid DateTime value.
Examples DTOT({^1998-02-16})    Returns: 02/16/1998 12:00:00am

GOMONTH( ) Function

Purpose Returns the date that is a specified number of months before or after a given Date or DateTime expression.
Syntax GOMONTH(<expD>, <expN>)
Returns Date
Parameter <expD> Specifies a Date expression.

<expN> Specifies the number of months from the date specified with <expD>. If <expN> is positive, the date returned by GOMONTH( ) is <expN> months after <expD>. If <expN> is negative, the date returned is <expN> months before <expD>.
Examples
GOMONTH({^1998-12-31}, 2) Returns: 02/28/1999
GOMONTH({^1998-12-31}, -2) Returns: 10/31/1998

HOUR( ) Function

Purpose Returns the hour portion from a DateTime expression.
Syntax HOUR(<expT>)
Returns Numeric
Parameter <expT> Specifies a DateTime expression from which HOUR( ) returns the hour.
Remarks HOUR( ) returns a numeric value based on a 24 hour format, and is not affected by the current setting of SET HOURS. For example, if SET HOURS is 12 or 24, the following command returns 13: HOUR({^1998-02-16 1:00p})
Examples HOUR({^1998-02-16 10:42a          Returns: 10

MAX( ) Function

This function is defined in String Functions under the heading MAX.

MDY( ) Function

Purpose Returns the specified Date or DateTime expression in Month Day Year format.
Syntax MDY(<expD>)
Returns Character
Parameter <expD> specifies the Date expression you want to return in Month Day Year format.
Remarks MDY( ) returns a Date expression in Month Day Year format with the name of the month spelled out. If SET CENTURY OFF, the resulting character expression has the format Month DD, YY. If SET CENTURY is ON, the format is Month DD, YYYY.
Examples
MDY(DATE()) Returns: September 02, 1999
MDY({^1999-4-19}) Returns: April 19, 1999

MIN( ) Function

This function is defined in String Functions under the heading MIN.

MINUTE( ) Function

Purpose Returns the minute portion from a DateTime expression.
Syntax MINUTE(<expT>)
Returns Numeric
Parameter <expT> Specifies the DateTime expression from which the minute portion is returned.
Examples MINUTE(({^1998-02-16 10:42a}    Returns: 42

MONTH( ) Function

Purpose Returns the numeric month for a given Date or DateTime.
Syntax MONTH(<expD>)
Returns Numeric
Parameter <expD> is the Date expression for which you want MONTH( ) to return the month number.
Remarks Returns a number from 1 through 12 indicating the month of the year. January is month 1, and December is month 12.
Examples
MONTH(DATE()) Returns: 9 if date is 09/02/1999
MONTH({^1992-5-3}) Returns: 5

SEC( ) Function

Purpose Returns the seconds portion from a DateTime expression.
Syntax SEC(<expT>)
Returns Numeric
Parameter <expT> Specifies the DateTime expression from which SEC( ) returns the second. If <expT> contains only a date and not a time, Visual FoxPro adds the default time of midnight (12:00:00 AM) to <expT>.
Examples SEC({^1998-08-21 10:42:16am}) Returns: 16

SECONDS( ) Function

Purpose Returns the number of seconds that have elapsed since midnight
Syntax SECONDS( )
Returns Numeric The numeric value is returned with a resolution of 1 millisecond, in the format seconds. thousandths of a second.)
Parameter None
Remarks The numeric value is returned with a resolution of 1 millisecond, in the format seconds. thousandths of a second.)
Examples
SECONDS() Returns: 35795.210
SECONDS()/(60 * 60) Returns: 9.948 = number of hours since midnight

TIME( ) Function

Purpose Returns the current system time in 24-hour, eight-character string (HH:MM:SS) format.
Syntax TIME([<expN>])
Returns Character
Parameter <expN>
Remarks The time returned includes hundredths of a second if <expN> is included. The numeric expression <expN> can be any value. However, the actual maximum resolution is about 1/18 second. Use SECONDS( ) for greater resolution.
Examples TIME()       Returns: 16:14:20

TTOC( ) Function

Purpose Converts a DateTime expression to a Character value of a specified format.
Syntax TTOC(<expT> [, 1 | 2])
Returns Character
Parameter <expT> Specifies a DateTime expression from which TTOC( ) returns a Character value. If <expT> contains only a time, Visual FoxPro adds the default date of 12/30/1899 to <expT>. If <expT> contains only a date, Visual FoxPro adds the default time of midnight (12:00:00 AM) to <expT>.

1 Specifies that TTOC( ) return a Character string in a format suitable for indexing. The character string has a 14-character yyyy:mm:dd:hh:mm:ss format that is not affected by the current settings of SET CENTURY or SET SECONDS.

2 Specifies that TTOC( ) return a Character string consisting of only the time portion of the DateTime expression. The settings of SET SECONDS and SET DATE specify if the seconds portion of the time is included in the character string. Note that if SET DATE is set to LONG or SHORT, the format of the character string is determined by the format of the Control Panel time setting.
Examples TTOC({^1998-08-21 10:42:16am})    Returns: ‘08/21/1998 10:42:16AM’

TTOD( ) Function

Purpose Returns a Date value from a DateTime expression.
Syntax TTOD(<expT>)
Returns Date
Parameter <expT> Specifies a Date and Time expression from which TTOD( ) returns a Date value. <expT> must evaluate to a valid DateTime. If <expT> contains only a time, Visual FoxPro adds the default date of 12/30/1899 to <expT> and returns this default date.
Examples TTOC({^1998-08-21 10:42:16am})    Returns: 08/21/1998

WEEK( ) Function

Purpose Returns a number representing the week of the year from a Date or DateTime expression.
Syntax WEEK(<expD> [, <expN1>] [, <expN2>])
Returns Numeric
Parameter <expD> Specifies the Date or DateTime expression for which WEEK( ) returns the week of the year. If you omit the optional <expN1> and <expN2> arguments, WEEK( ) uses Sunday as the first day of the week.

<expN1> Specifies the requirements for the first week of the year. <expN1> may be one of the following values.

<expN1> Description
0 WEEK( ) returns whatever week is currently selected in the First Week of Year list box on the International tab in the Options dialog box.
1 First week contains January 1st. This is the default when you omit <expN1>.
2 The larger half (four days) of the first week is in the current year.
3 First week has seven days.

<expN2> Specifies the first day of the week. <expN2> may be one of the following values.

<expN2> Description
0 WEEK( ) returns whatever day is currently selected in the Week Starts on list box on the International tab in the Options dialog box.
1 Sunday. This is the default when you omit <expN1>, and is the first day of the week used in earlier FoxPro versions.
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
Remarks WEEK( ) returns a number from 1 to 53 that represents the week of the year. For example, WEEK( ) returns 1 for the first week of the year, 2 for the second week of the year, and so on. Note that a week can be split between years — the first week of the year can be in the current year and the previous year.
Examples WEEK({^1998-02-16})   Returns: 8

YEAR( ) Function

Purpose Returns the year from the specified Date or DateTime expression.
Syntax YEAR(<expD>)
Returns Numeric
Parameter <expD>

Specify a Date expression with <expD>. The Date expression may be a function that returns a date, or a date type memory variable, array element or field. It can also be a literal date string (for example, {06/06/92}).
Remarks Always returns the year with the century. The CENTURY setting (ON or OFF) doesn't affect the returned value.
Examples
YEAR({^1998-06-06}) Returns: 1998
YEAR(date) If date is 4/25/85, it returns 1985.