Date functions

Overview

Data Management's Date functions extract and format date information, return the current date, and calculate date-based values.

AddDays

Adds the number of days in number to date, returning a new date.

Syntax

AddDays( date, number )

The AddDays function syntax has the following arguments:

Part	Description
`date`	Required. Must be of type Date.
`number`	Required. Must be of type Integer, Decimal, or Float.

Examples

AddDays("20 Feb 1999", 20) returns 12 Mar 1999.
AddDays("20 Feb 2000", 20) returns 11 Mar 1999.
AddDays("31 Dec 1999", 365) returns 30 Dec 2000.
AddDays("31 Dec 2000", 365) returns 31 Dec 2001.

AddMonths

Adds the number of months in number to date, returning a new date.

Syntax

AddMonths( date, number )

The AddMonths function syntax has the following arguments:

Part	Description
`date`	Required. Must be of type Date.
`number`	Required. Must be of type Integer, Decimal, or Float.

Remarks

Adds the number of months in number to the date, producing a new date without changing the day, unless the day of the resulting date is invalid.

Examples

AddMonths("31 Jan 1999", 1) returns 28 Feb 1999.
AddMonths("30 Mar 1999", -1) returns 28 Feb 1999.
AddMonths("29 Feb 2000", 12) returns 28 Feb 2001.
AddMonths("29 Feb 2000", 48) returns 29 Feb 2004.

AddYears

Adds the number of years in number to date, returning a new date.

Syntax

AddYears( date, number )

The AddYears function syntax has the following arguments:

Part	Description
`date`	Required. Must be of type Date.
`number`	Required. Must be of type Integer, Decimal, or Float.

Remarks

Adds the number of years in number to the date, producing a new date without changing the day, unless the day of the resulting date is invalid.

Examples

AddYears("31 Jan 1999", 1) returns 31 Jan 2000.
AddYears("31 Jan 1999", -1) returns 31 Jan 1998.
AddYears("29 Feb 2000", 1) returns 28 Feb 2001.
AddYears("29 Feb 2000", 4) returns 29 Feb 2004.

AsDate

Attempts to interpret value as a Date value. If value is of type Text, converts value as specified by format_string argument.

Syntax

AsDate( value )
AsDate( value, format_string )

The AsDate function syntax has the following arguments:

Part	Description
`value`	Required. May be of type Integer, Float, Date, DateTime, or Text.
`format_string`	Required if value is of type Text. Must be a valid combination of symbols from the list below.

Format symbol	Meaning
`M`	Numeric month (7).
`MM`	Numeric month with leading zero (07).
`MMM, Mmm, mmm`	Abbreviated month name (JUL, Jul, jul,).
`MMMM, Mmmm, mmmm`	Full month name (JULY, July, july).
`D`	Day of month (7, 24).
`DD`	Day of month with leading zero (07 or 24).
`WW, Ww, ww`	Abbreviated day of week (FRI, Fri, fri).
`WWW, Www, www`	Full day of week (FRIDAY, Friday, friday).
`YY`	Last two digits of the year (98).
`YYYY`	Four-digit year (1998).

Remarks

If value is an Integer or Float, then value is interpreted as a Julian value (the number of days since Jan 1, 1600). If value is a DateTime, then the Date portion is extracted. The format used by the value returned is the same as the format_string argument, so for example AsText(AsDate(CurrentDate(), "MMDDYY")) will return something like 080307 rather than Aug 03 2007.

AsDate() and Julian() are the inverse of each other: AsDate(Julian(DATE)).

Examples

The following will all produce the same valid date results:

AsDate("1/15/08", "M/D/YY")
AsDate("01/15/2008", "MM/DD/YYYY")
AsDate("15 jan 08", "DD mmm YY")
AsDate("JANUARY 15, 2008", "MMMM D, YYYY")

The following will all produce invalid date results. You can test for valid results with IsValid.

AsDate("03 13 2000", "DDMMYY")
AsDate("Acme Software")

CurrentDate

Returns a Date that is today's date.

Syntax

CurrentDate( )

Remarks

If you assign CurrentDate to a text field, it will be converted to text in the format DDMMYYYY (03 Jan 2024). If you assign CurrentDate to a Date (or other type) field, it will be stored in the format of the field.

Examples

Year(CurrentDate()) returns the current year.
CurrentDate() -- DATE returns the number of days elapsed since DATE (which is presumed to be a field of type Date). Note that you don't need to use Julian to subtract dates.
MonthName(CurrentDate()) returns the name of the current month.

Day

The Day-of-the-month portion of date is extracted as a number in the range 1-31.

Syntax

Day( date )

The required argument date must be of type Date or DateTime.

Examples

Day(CurrentDate()) returns the current day-of-the-month.
Day(START_DATE) returns the day-of-the-month portion of a field of type Date.

DayOfWeek

The Day Of Week is extracted from date and returned as a number in the range 1-7.

Syntax

DayOfWeek( date )

The required argument date must be of type Date or DateTime.

Examples

DayOfWeek(CurrentDate()) returns the current day-of-the-week.
DayOfWeek(DATE) = 3 tests to see if DATE is Tuesday.

DayOfWeekAbbr

The Day Of Week is extracted from date and returned as an abbreviation (Sun, Mon, Tue, and so forth).

Syntax

DayOfWeekAbbr( date )

The required argument date must be of type Date or DateTime.

Examples

DayOfWeekAbbr(CurrentDate()) returns the current day-of-the-week.
DayOfWeekAbbr(START_DATE) returns the day-of-the-week portion of a field of type Date.

DayOfWeekName

The Day Of Week is extracted from date and returned as a name (Sunday, Monday, and so forth).

Syntax

DayOfWeekName( date )

The required argument date must be of type Date or DateTime.

Examples

DayOfWeekName(CurrentDate()) returns the current day-of-the-week.
DayOfWeekName(START_DATE) returns the day-of-the-week portion of a field of type Date.

DiffDays

Returns the integer difference between two dates in days.

Syntax

DiffDays( date1, date1 )

The required arguments date1 and date2 must be of type Date.

Remarks

Returns the integer difference in days between date1 and date2. The result is negative if date1 is before date2, and positive if date1 is after date2.

Examples

DiffDays("20 Jan 1999", "10 Jan 1999" returns 10 days.
DiffDays("1 Jan 1999", "1 Feb 1999") returns -31 days.
DiffDays("10 Mar 2001", "10 Mar 2000") returns 365 days.
DiffDays("10 Mar 2000", "10 Mar 1999") returns 366 days.

DiffMonths

Returns the integer difference between two dates in months.

Syntax

DiffMonths( date1, date1 )

The required arguments date1 and date2 must be of type Date.

Remarks

Returns the integer difference in months between date1 and date2. The day of the dates is ignored. The result is negative if date1 is before date2, and positive if date1 is after date2.

Examples

DiffMonths("10 Jan 1999", "20 Jan 1999" returns 0 months.
DiffMonths("20 Jan 1999", "10 Jan 1999" returns 0 months.
DiffMonths("1 Jan 1999", "1 Feb 1999") returns -1 months.
DiffMonths("1 Jan 2000", "1 Jan 1999") returns 12 months.

DiffYears

Returns the integer difference between two dates in years.

Syntax

DiffYears( date1, date1 )

The required arguments date1 and date2 must be of type Date.

Remarks

Returns the integer difference in years between date1 and date2. The day and month of the dates are ignored. The result is negative if date1 is before date2, and positive if date1 is after date2.

Examples

DiffYears("10 Jan 1999", "20 Jan 1999" returns 0 years.
DiffYears("10 Jan 1999", "20 Dec 1999" returns 0 years.
DiffYears("1 Feb 1999", "1 Dec 1998") returns 1 year.
DiffYears("1 Feb 1999", "1 Feb 1996") returns 3 years.

FormatDate

Formats date, using the specified format_string, and returns a text value.

Syntax

FormatDate( date, format_string )

See Configuring Date, Time, and Date-Time data types for details on format strings.

Julian (date)

Returns an Integer that is the difference in days between the date argument and Jan 1, 1600.

Syntax

Julian( date )

The required argument date must be of type Date.

Example

Julian(ENDDATE) -- Julian(STARTDATE) finds the difference, in days, between two dates. ENDDATE -- STARTDATE is simpler and gives you the same results.

MakeDate

Constructs a Date using the specified day, month, and year arguments.

Syntax

MakeDate( day, month, year )

The MakeDate function syntax has the following arguments:

Part	Description
`day`	Required. Must be Integer or Float in range 1 – 31.
`month`	Required. Must be Integer or Float in range 1 – 12.
`year`	Required. Must be Integer or Float.

Example

OrderDate - (MakeDate(1, 1, 2000)) returns the difference in days between OrderDate and January 1, 2000.

Month

The Month portion of date is extracted as a number in the range 1– 12.

Syntax

Month( date )

The required argument date must be of type Date or DateTime.

Examples

Month(STARTDATE) = MONTH(ENDDATE) tests whether two dates have the same month.
MakeDate(1, Month(CurrentDate()), Year(CurrentDate()) ) returns a Date which is the first day of the current month.

MonthAbbr

The Month portion of date is extracted as title-case three-character string (for example, Jan).

Syntax

MonthAbbr( date )

The required argument date must be of type Date or DateTime.

Remarks

The function MonthAbbr is equivalent to SubString(MonthName(date), 1, 3).

Examples

AsText(MonthAbbr(ENDDATE)) + " " + AsText(Year(ENDDATE)) formats just the month abbreviation and year portions of a field of type Date.

MonthName

The Month portion of date is extracted as title-case string (for example, January).

Syntax

MonthName( date )

The required argument date must be of type Date or DateTime.

Examples

AsText(MonthName(ENDDATE)) + " " + AsText(Year(ENDDATE)) formats just the month and year portions of a field of type Date.

ScanDate

Converts date_string, using the specified format_string, and returns a Date.

Syntax

ScanDate( date_string, format_string )

Example

ScanDate( "2024-11-18", "YYYY-DD-MM" ) returns 18 Nov 2024.

See Configuring Date, Time, and Date-Time data types for details on format strings.

Year

The Year portion of date is extracted as an Integer. Years are four-digit years.

Syntax

Year( date )

The required argument date must be of type Date or DateTime.

Examples

Year(CurrentDate()) -- Year(CurrentDate()) / 100 * 100 finds the current two-digit year (year without the century part).
MakeDate(1, 1, Year(CurrentDate())) returns a Date which is the first of the current year.