Date Scalar Functions

AddMonths (d,n)

Adds n months to date d.

Example:

AddMonths ('5/13/99',4) = 9/13/99

DayOfMonth (d)

Returns the day of month for date d.

Example:

DayOfMonth ('11/02/99') = 2

LastDay (d)

Returns date of the last day of the month containing date d.

Example:

LastDay ('12/6/9') = 12/31/99

MonthsBetween (d1,d2)

Returns the number of months between dates d1 and d2 as a real number (fractional value).

Example:

MonthsBetween ('12/5/99','5/6/99') = 6.9677

NextDay (d,s)

Returns the date of the first weekday s after date d. If s is omitted, add one day to d.

Example:

NextDay ('12/16/99',"Monday") = 12/22/99

Sysdate ()

Returns the current system date and time for each record in item c.

Example:

Sysdate() = 2/11/96 19:54:36

ToChar (d/n, 'f' or "f")

Converts the date or number d/n into a string in the specified format. This function does not change the data, but rather the item data type. The results cannot be computed mathematically.

Example:

ToChar ('05/08/06', "mmmm-yy" ) = May-06

If you are referencing a Date or Number column for the first argument (Date field), single quotes are not required. If you are passing a data value, single quotes are required.

A comma must immediately follow the first argument. In the second argument (Format field), single quotes or double quotes must enclose values.

ToDate (s)

ToMonth (d)

Returns the referenced date and replaces the day and year part of the date with 15 and 1904 respectively. You can change the value to display as a month string by adding and applying a mmm date format or a month number by applying the date format of mm.

Example:

ToMonth ('11/2/09' 09:46:00 AM) = 11/2/09 ~ Nov

ToQtr (d)

Returns a string quarter value for each value of d.

Example:

ToQtr ('11/02/06' 09:46:00 AM) = Q3

ToYear (d)

Returns the integer year for each value of d. You can convert the year to display without commas by applying the 0 numeric format.

Example:

ToYear ('11/02/06' 09:46:00 AM) = 2,006 ~ 2006