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 |
---|---|
| Numeric month (7). |
| Numeric month with leading zero (07). |
| Abbreviated month name (JUL, Jul, jul,). |
| Full month name (JULY, July, july). |
| Day of month (7, 24). |
| Day of month with leading zero (07 or 24). |
| Abbreviated day of week (FRI, Fri, fri). |
| Full day of week (FRIDAY, Friday, friday). |
| Last two digits of the year (98). |
| 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 2007). 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.
ScanDate( date_string, format_string )
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.