Date Functions

Date functions are tools used for manipulating dates.

Date Function Syntax

The syntax for many date functions is:

FunctionName(<dDate>) --> Result

where:

• FunctionName is the name of the function (for example, CDOW),

• (<dDate>) is the date variable or date field (for example, Today or Opened),

• and Result is the value returned from the function.

For example, CDOW( Today ) will return something like "Monday."

Most functions return a character string, but some return a number, such as Year(<dDate>) or DOW(<dDate>), which returns the Day Of Week as a number between 1 and 7.

Common Date Functions

The following lists common date functions that can be used in Abacus to manipulate date fields. Function names are NOT case sensitive and can be upper or lower case.

NOTE: The most common functions are Day, Month, and Year.

AGE(dStartDate)

Returns the age in years of the person (or thing) since the given date. So, for example if you have a Birthday field called DOB, you would use Age(DoB) to know how old someone is. This function would return the number of years between the DoB field and Today’s date. For durations between two dates, like the length of a marriage, you can pass the second date as in AGE(dStartDate, dEndDate).

You could also calculate the residual months (in some divorce cases, you might need the number of years and remaining months) by adding an "M" as the third parameter, like this: Age (dStartDate, dEndDate,"M" ). This function returns the remainder months after the years have been extracted.

CDOW(dDate)

[Character Day of Week] Returns the conversion of a date to the day of week in word format, for example Tuesday. CDOW(Today) might return Monday.

CMONTH(dDate)

[Character-Month] Returns the name of the month, for example, January or June.

CToD(dDate)

[Character to Date] Converts a character string to a date expression. CTOD("01/01/08") would return a date variable.

CtoMonth(cMonthName)

Returns the number of a month from its name.

DATE()

Return your operating system’s current date.

DAY(dDate)

Returns the day of the month as a number, for example 1 or 15.

DAYSAWAY(dDate)

Returns the same text seen on the Event window, showing how far away a date is. This function might return any of the following: Today, Yesterday, Tomorrow, x days past, x days to go.

DOW(dDate)

Returns the numeric value for the day of week, for example, 1 (for Sunday) or 2 (for Monday).

DTOC(dDate)

[Date to Character] Converts a date to a character string, for example, DTOC(When) would return characters for an event date. This is necessary when combining a date field with character fields for a long expression, as in trim(events->What)+" "+dtoc(events->When).

DTOS(dDate)

[Date to String] Converts a date to a character string in the format CCYYMMDD, for example, 20000130. This is needed for index expressions so the date will sort correctly (DTOC fails because the year is not in the front).

DTOW(dDate)

[Date to Words] Converts a date to word format, for example January 1, 2009.

FullMonth(xmonth)

Takes a number or partial month name (for example, "Jan") and returns the full month name.

MakeDate()

Provides the ability to calculate more unusual requests based on a given day, month and/or year. Click here for more information on this powerful function.

MONTH(dDate)

Converts a date expression to a month number.

NEXTBDAY(dDate)

Returns the date of the next occurrence of a birthday – later this year or into the next. This date function lets you filter for coming birthdays, wedding anniversaries, etc. For example, if DoB were a Date-of-Birth date field you added to the Names database, you can print a list of all people with birthdays coming up in the next two weeks by setting an expert query to: NextBDay(Names->BoD) - Today < 14.

NToCMonth(nMonthNumber)

Returns the month name from its number.

NUM2TEXT(nNumber)

Converts a number into text format. For example, 301 would convert to "Three hundred one." Passing a second "True" parameter returns an ordinal number instead of a cardinal number: NUM2TEXT( 301, .t.) returns "Three hundred first." So, the full text for the current year would be NUM2TEXT( Year(Today) )

YEAR(dDate)

Converts the year portion of a date into a numeric value, for example YEAR(DoB) would return 1921 if the DoB field had a date in that year. You might use this in a query to find all people born after 1921, using this logical expression: YEAR(DoB) > 1921.