Functions

Aggregate functions

See Aggregate functions for details.

Date functions

YEARPART

YEARPART(date)

Given a date, returns the year of the date as a number.

MONTHPART

MONTHPART(date)

Given a date, returns the month in the date as a number.

DAYPART

DAYPART(date)

Given a date, returns the day of the date as a number.

DATEPART

DATEPART(date)

Given a date, returns the date part as a date i.e. removes any minutes from the date so the value represents midnight at the beginning of that day.

TIMEPART

TIMEPART(date)

Given a date, returns the time part as a number in minutes e.g. if the date is 01/02/2008 12:34 this returns 754.0 – the number of minutes after midnight.

YEARMONTHPART

YEARMONTHPART(date)

Given a date, returns the year and month part as a string: yyyy/mm. If the month is January - September a 0 prefix is applied, e.g. 2006/02.

MONTHYEARPART

MONTHYEARPART(date)

Given a date, returns the month and year part as a string: mm/yyyy. If the month is January - September a 0 prefix is applied, e.g. 02/2006

MONTHNAME

MONTHNAME(date)

Given a date, returns the month part as the full name of the month (in the current locale)

SHORTMONTHNAME

SHORTMONTHNAME(date)

Given a date, returns the month part as the abbreviated name of the month (as determined by the locale and how Windows abbreviates it)

DAYNAME

DAYNAME(date)

Given a date, returns the day part as the full name of the day (in the current locale)

SHORTDAYNAME

SHORTDAYNAME(date)

Given a date, returns the day part as the abbreviated name of the day (as determined by the locale and how Windows abbreviates it)

NOW

NOW()

Returns the current date and times as a date.

YEARSDIFF

YEARSDIFF(from,to)

Returns the number of complete years between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved i.e. if from is 01/05/2008 at 12:34 and ‘to’ is 01/05/2009 at 12:29, this will count as a full year and therefore return 1.

MONTHSDIFF

MONTHSDIFF(from,to)

Returns the number of complete months between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.

DAYSDIFF

DAYSDIFF(from,to)

Returns the number of complete days between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.

INYEAR

INYEAR(date,number)

Returns true if the date is in the year given as a number e.g.

INYEAR(when_surveyed,1993)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INMONTH

INMONTH(date,month,year)

Returns true if the date is in the year and month given as numbers e.g.

INMONTH(when_surveyed,3,1993)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INYEARS

INYEARS(date,startyear,endyear)

Returns true if the date is in a year between the start and end years inclusive e.g.

INYEARS(when_surveyed,1993,1995)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INMONTHS

INMONTHS(date,startmonth,startyear,endmonth,endyear)

Returns true if the date is between the start month in the start year and the end month in the end year inclusive e.g.

INMONTHS(when_surveyed,10,1993,2,1994)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

ISDATE

ISDATE(putative_date)

If the field is a date because it has come from the database returns true, if it is a string then returns true if it can be converted into a date, otherwise returns false.

NUMTOMONTHNAME

NUMTOMONTHNAME(n)

Returns the month name given an integer from 1 to 12.

NUMTOSHORTMONTHNAME

NUMTOSHORTMONTHNAME(n)

Returns the short version of a month name (e.g. Jan) given an integer from 1 to 12.

TODATE

TODATE(year,month,day)

Returns the date given the year, month and day as integers.

TODATETIME

TODATETIME(year,month,day,hours,minutes)

Returns the date given the year, month, day, hours and minute as integers.

DATEFORMAT

DATEFORMAT(date,dateformat)

Given a 'date' value, this formats the date part of it according to the second parameter. If the first parameter is not a date or the second is not a string then null is returned.

This uses the Win32 API GetDateFormat function, so you can search for more details concerning e.g. the behaviour in other languages.

TIMEFORMAT

TIMEFORMAT(date,timeformat)

Given a 'date' value, this formats the time part of it according to the second parameter. If the first parameter is not a date or the second is not a string then null is returned.

This uses the Win32 API GetTimeFormat function, so you can search for more details concerning e.g. the behaviour in other languages.

These characters are case sensitive. Any other character is passed through to the output unchanged. If you were to put more than 2 of these letters consecutively they are treated as being the same as 2 of that letter.

DATETIMEFORMAT

DATETIMEFORMAT(date,dateformat,timeformat)

Given a date and a date format and a time format, this returns a string consisting of the date part of the 'date' value formatted as it would be by DATEFORMAT, followed by a space, followed by the time part of the 'date' value formatted as it would be by TIMEFORMAT.

DAYOFWEEK

DAYOFWEEK(date)

Given a date, returns the day-of-the-week of the date as a number according to the ISO8601 standard (from 1=Monday to 7=Sunday).

DAYOFYEAR

DAYOFYEAR(date)

Given a date, returns the day-of-the-year of the date as a number (1=January 1st).

DAYSINYEAR

DAYSINYEAR(date)

Given a date, returns the number of days in the year of the date.

List variable functions

See List Variables for details.

Trigonometric and logarithmic functions

Trigonometric functions are functions of an angle.

All these functions return NULL if their parameters cannot be converted into numbers. There may be further restrictions on the parameters as described below.

Please note that all angles in the trigonometric functions are expressed in degrees.

ACOS

ACOS(x)

Calculates the inverse cosine of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range 0 to 180 degrees.

ASIN

ASIN(x)

Calculates the inverse sin of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range -90 degrees to 90 degrees.

ATAN

ATAN(x)

Calculates the inverse tangent of x. Returns a value within the range -90 degrees to 90 degrees.

ATAN2

ATAN2(x,y)

Calculates the inverse tangent of x / y using the signs of x and y to correctly determine the quadrant. Returns a value within the range -180 degrees to 180 degrees.

COS

COS(x)

Calculates the cosine of x.

EXP

EXP(x)

Calculates e^x.

GAMMALN

Calculates the natural logarithm of the gamma function, ?(x). Returns NULL if x<=0.

LOG

LOG(x)

Calculates the log (base 10) of x. Returns NULL if x cannot be converted into a number, or x<=0.

LOGE

LOGE(x)

Calculates the log (base e) of x, otherwise known as a natural logarithm. Returns NULL if x<=0.

SIN

SIN(x)

Calculates the sin of x.

TAN

TAN(x)

Calculates the tangent of x. Returns NULL if cos(x) = 0 (and therefore tan(x) would be infinite).

String manipulation

LEN

LEN(string)

Returns the length of a string i.e. how many characters long the string is, so LEN('MYNODEID’' would be 8. This is often used in conjunction with other string manipulation functions described below. The function LEN may also be used to find the length of a list variable. List variables and the meaning of the LEN function in that context are described later in the paper.

LEFT

LEFT(string,number of characters)

Returns a string containing the first n characters in a string, where n is the second parameter passed to the function e.g. LEFT('MX11112222',2) would be 'MX'. If the second parameter is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.

RIGHT

RIGHT(string, number of characters)

Returns a string containing the last n characters in a string, where n is the second parameter passed to the function e.g. RIGHT('MX11112222') would be '11112222'. If the second parameters is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.

MID

MID(string,start position, number of characters)

Returns a string containing a number of characters from a string starting at a given position. The positions start from 1 for the first character, with 2 for the second etc. so MID('MX11112222',1,2) giving 'MX', MID('MX11112222',3,4) giving '1111' and MID('MX11112222',7,4) giving '2222'.

If the second parameter is 0 or less or greater than the number of characters in the string then the empty string is returned. If the third parameter is 0 or less than the empty string is returned. If the number of characters given in the third parameter is greater than the number of characters remaining starting from the second parameter, then the rest of the string is returned e.g. MID('MX12345678',9,999) would return '78', in this case the rest of the string starting from the 9^th character.

SUBST

SUBST(string,thing to replace, thing to replace it with)

Replaces the first instance of the second parameter in the first with the third e.g. SUBST('01880132','01','ND') returns 'ND880132'

GSUBST

GSUBST(string, thing to replace, thing to replace it with)

Replaces all instances of the second parameter in the first with the third e.g. GSUBST('01880132','01','ND') returns 'ND88ND32'

GENSUBST

GENSUBST(string, regexp, format)

Replaces the string with the format defined if regular expression regxp matches the string, otherwise returns the string unchanged. e.g.

SET user_text_1 = GENSUBST(node_id,'SK([0-9]*)','99\1') will set user_text_1 to the node_id unless the node_id matches the regular expression 'SK([0-9]*)', otherwise will set user_text_1 to 99 followed by the numerical part of the node_id following the SK.

The regular expression may contain a number of bracketed sub-expressions. In this case there is one bracketed sub-expression ([0-9]*). The bracketing does not change what the regular expression will match, so 'SK([0-9]*)' will match the same things as will 'SK[0-9]*', however each sub-expression is given a number, starting with 1, and the text that matches that sub-expression may be used in the format by using that number preceded with a backslash. In this case what is happening is that a node ID consisting of the characters SK followed by a number of digits is replaced in the result of the function with the digits 99 followed by the string of digits from the node_id e.g. SK12345678 becomes 9912345678.

NL

NL()

The NL function returns a new-line character e.g. to set up a 3 line note field use something like

SET notes='THIS'+NL()+'THAT'+NL()+'THE OTHER'

Number conversion and formatting

FIXED

FIXED(number to convert,number of decimal places)

Given a number and a number of decimal places from 0 to 8, convert the number to a string with that number of decimal places, rounding up or down as appropriate. If the number of decimal places is 0 the string will have no decimal point.

e.g.

FIXED(1.9,3) is "1.900"

FIXED(1.9991,3) is "1.999"

FIXED(1.9999,3) is "2.000"

Numerical functions

ABS

ABS(number)

Calculates the absolute value of a number i.e. if the number is less than zero returns negative 1 times the number, turning it positive e.g. ABS(-3) = 3. If the number is zero or above it is unchanged.

e.g. SET user_number_1 = ABS(ground_level)

INT

INT(number)

INT takes the integer part of a number. If the number is an integer it will remain unchanged.

FLOOR

FLOOR(number)

FLOOR returns the closest integer less than or equal to its parameter. Like INT it leaves integers unchanged. The difference between INT and FLOOR is that, whilst they will return the same value for positive numbers, INT will, by removing the fractional part of a number, make negative numbers larger (e.g. INT(-123.45) will be 123). FLOOR, on the other hand will make negative numbers more negative (e.g. FLOOR(-123.45) will be –124.

CEIL

CEIL(number)

CEIL returns the closed integer greater than or equal to its parameter. Like INT it leaves integers unchanged.

Conditional expression

IIF

IIF(condition,first alternative, second alternative)

The IIF function returns the value of the second parameter if the first expression evaluates as true, otherwise returns the third. e.g.

IIF(service_total_score>total_score,service_total_score,total_score)>20 will select those objects with the maximum of the service_total_score and the total_score being greater than 20

NVL

NVL(expr1,expr2)

The NVL function returns the value of expr2 if expr1 is null. If expr1 is not null, then NVL returns expr1.