Supported Functions Index

• Aggregate Functions
• Mathematical Functions
• String Functions
• Date and Time Functions
• Conversion Functions
• Domain Functions
• Pocket Outlook Functions
• Other Functions

Please note: the version of SprintDB Pro required by the function. Version 1.5 and higher will not work in version 1.0.

Click here for a summary of New Features in versions 1.5 - 1.7x.

Version 2.0+ features are covered here.

Aggregate Functions

AVG, MAX, MIN, RECCOUNT, RECNO

AVG (expression)

Returns the average of the values in a group.

Examples

AVG (table1.amount)

AVG (table1.sales/&form_field1) WHERE table1.dept='sales'

MAX (expression)

Returns the maximum value in the expression.

Examples

MAX (table1.amount)

MAX (table1.sales1+table1.sales2) WHERE table1.sales1>1000

MIN (expression)

Returns the minimum value in the expression.

Examples

MIN (table1.amount*&form_field1)

MIN (table1.sales1+table1.sales2) WHERE table1.sales1>1000

RECCOUNT ( [form_id] )

Returns the number or records.

form_id: 0 - MainForm, 1 - Subform1, 2 - Subform2, ...
You can omit the form_id parameter. It's 0 (MainForm) by default.

RECNO ( [form_id] )

Returns the current record number.

form_id: 0 - MainForm, 1 - Subform1, 2 - Subform2, ...
You can omit the form_id parameter. It's 0 (MainForm) by default.

< Top>
 

Mathematical Functions

ABS, ACOS, ASIN, ATAN, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, POWER, ROUND, SIN, SINH, TAN, TANH, TRUNC

ABS (n)

Returns the absolute, positive value of n.

Example

ABS (-1.0)

ABS (table1.number_field1)

ACOS (n)

Returns the arccosine of n. n is a angle in radians.

Example

ACOS (.3) - returns 1.266104

ASIN (n)

Returns the arcsine of n. n is a angle in radians.

Example

ASIN (.3) - returns 0.304693

ATAN (n)

Returns the arctangent of n. n is a angle in radians.

Example

ATAN (.3) - returns 0.291457

CEIL (n)

Returns the smallest integer greater than or equal to n.

Example

CEIL (12.8) - returns 13

COS (n)

Returns the cosine of n. n is a angle in radians.

Example

COS (180*3.14159265359/180) - returns -1

COSH (n)

Returns the hyperbolic cosine of n. n is a angle in radians.
 

EXP (n)

Returns the exponential value of n.

Example

EXP (4) - returns 54.59815

FLOOR (n)

Returns the largest integer less than or equal to n.

Example

FLOOR (15.7) - returns 15

LN (n)

Returns the natural logarithm of n.

Example

LN (5.175643) - returns 1.64396

LOG (m,n)

Returns the base-m logarithm of the n.

Example

LOG (10,100) - returns 2

POWER (m,n)

Returns the value of m raised to the power of n.

Example

POWER (3,2) - returns 9

ROUND (n,m)

Returns the value of n, rounded to the specified length m. When m is a negative number, n is rounded on the left side of the decimal point, as specified by m.

Examples

ROUND (15.193,1) - returns 15.2

ROUND (15.193,-1) - returns 20

SIN (n)

Returns the trigonometric sine of n. n is a angle in radians.

Example

SIN(30*3.14159265359/180) - return 0.5

SINH (n)

Returns the hyperbolic sines of n.

Example

SINH (1) - returns 1.17520119

SQRT (n)

Returns the square-root of n.

Example

SQRT (26) - returns 5.09901951

TAN (n)

Returns the tangent of n. n is a angle in radians.

Example

TAN (3.14159265359/4) - returns 1

TANH (n)

Returns the hyperbolic tangent of n. n is a angle in radians.
 

TRUNC (n,m)

Returns the value of n, truncated to the specified length m. When m is a negative number, n is truncated on the left side of the decimal point, as specified by m.

Examples

TRUNC (15.79,1) - returns 15.7

TRUNC (15.79,-1) - returns 10
<Top>
 

String Functions

INITCAP, INSTR, LEFT, LENGTH, LOWER, LTRIM, REPLACE, RIGHT, RTRIM, SUBSTR, UPPER

INITCAP (char_expr)

Capitalizes the first character of each word and makes the rest of each word lower case.

Example

INITCAP ('abc '+'DEF') - returns "Abc Def"

INSTR (char_expr1,char_expr2[,n[,m]])

Searches for the mth occurrence of charexpr2 after position n of charexpr1 and return the position. n and m can be omitted and default is 1. If n is negative, INSTR searches from the end of charexpr1. If search fails, returns 0.

Examples

INSTR ('CORPORATE FLOOR','OR',3,2) - returns 14

INSTR ('CORPORATE FLOOR','OR',-3,2) - returns 2

LEFT (char_expr,int_expr)

Returns the first int_expr characters from char_expr.

Examples

LEFT ('hello',3) - returns "hel"

LEFT (table1.char_field1,5)

LENGTH (char_expr)

Returns the number of characters.

Examples

LENGTH ('hello') - returns 5

LENGTH (table1.field1)

LOWER (char_expr)

Returns char_expr in lowercase letters.

Example

LOWER ('Hello!') - returns "hello!"

LTRIM (char_expr)

Returns char_expr with leading blanks removed.

Example

LTRIM (' hello') - returns "hello"

REPLACE (char_expr1,char_expr2,char_expr3)

Replaces all occurrences of char_expr2 in char_expr1 with char_expr3.

Example

REPLACE ('abcdefghicde','cde','xxx') - returns "abxxxfghixxx"

RIGHT (string, length)

Returns a specified number of characters from the right side of a string.

Example

RIGHT("hello",3) - returns "llo".

RTRIM (char_expr)

Returns char_expr with trailing blanks removed.

Example

RTRIM ('hello ') - returns "hello"

SUBSTR (char_expr1,int_expr2[,int_expr3])

Returns int_expr3 characters from int_expr2 position of char_expr1. If int_expr3 is ommited, characters are returned until the end of char_expr1. If int_expr2 is negative, the position is counted from the end of char_expr1. The first character of string is position 1.

Examples

SUBSTR ('ABCDEFG',3,4) - returns "CDEF"

SUBSTR ('ABCDEFG',-5,4) - returns "CDEF"

UPPER (char_expr)

Returns char_expr in uppercase letters.

Example

UPPER ('Hello!') - returns "HELLO!"

< Top>
 

Date and Time Functions

ADD_DATE, ADD_MONTHS, DAY, DATEADD, DATEDIFF, DATEPART, DATESET, GetDayOfWeek, GetDayOfWeekStr, GetMonthName, LAST_DAY, MONTH, SYSDATE, SYSDATETIME, SYSTIME, YEAR, DIFF_TIME

ADD_DATE (datetime, interval)

Returns a date to which a specified time interval has been added. Interval is a string expression( "[+/-][nnd][nnh][nnm][nns]" ).

(nn=number, d=Day, h=Hour, m=Minute, s=Second)

Examples

ADD_DATE('10/1/01 12:30:40','1d1h1m1s') - returns '10/2/01 13:31:41'.

ADD_DATE('10/1/01 12:30:40','-1h' ) - returns '10/1/01 11:30:40'.

ADD_MONTHS (d,n)

Adds n months to the specified date.

Example

ADD_MONTHS (TO_DATE('4/15/01'),3) - returns "7/15/01"

DAY (d)

Returns the day of the specified date.

Example

DAY (TO_DATE('4/15/01')) - returns 15

DATEADD (interval, number, date)

Returns a date to which a specified time interval has been added.

Arguments:

Interval: the interval of time you want to add. y - year, m - month, d - day, h - hour, n - minute, s - second

Number: the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

Date: date to which the interval is added.

Examples

DATEADD(d,10,"02/10/2002")

DATEADD(m,3,!Table1.Date1)

DATEADD(s,300,SYSDATETIME())

DATEDIFF (interval, date1, date2)

Returns the number of time intervals between two specified dates.

Arguments:

Interval: the interval of time you want to calculate the difference between date1 and date2. y - year, m - month, d - day, h - hour, n - minute, s - second

date1, date2: two dates you want to use in the calculation.

Example

DATEDIFF(m,"11/10/2001","02/10/2002")

DATEDIFF(s,!Table1.Date1,SYSDATETIME())

DATEPART (interval, date)

Returns a number containing the specified part of a gived date.

Arguments:

Interval: the interval of time you want to return. y - year, m - month, d - day, h - hour, n - minute, s - second

Date: date that you want to evaluate.

Example

DATEPART(h,SYSDATETIME())

DATESET (interval, number, date)

Returns a date to which a specified time interval has been set.

Arguments:

Interval: the interval of time you want to add. y - year, m - month, d - day, h - hour, n - minute, s - second

number: the number of intervals you want to set.

Date: date that you want to evaluate.

Examples

DATESET(m,9,"03/01/2001") returns "09/01/2001".

DATESET(d,9,"03/01/2001") returns "03/09/2001".

GetDayOfWeek (date) [Version 1.5 and above]

Returns the day of the week. 1 = sunday, 2 = monday, and so on.

Arguments:

Date: a date expression.

Examples

GetDayOfWeek( SYSDATE() )
GetDayOfWeek( "03/01/2001" )

GetDayOfWeekStr (date[,type]) [Version 1.5 and above]

Arguments:

Date: a date expression.
Type: "Long" returns the long name for the day of the week. "Short" returns the abbreviated name for the day of the week. Default is "Short".

Examples

GetDayOfWeekStr (SYSDATE())
GetDayOfWeekStr ("05/05/2002", Long) returns "Sunday"
GetDayOfWeekStr ("05/05/2002", Short) returns "Sun"

GetMonthName ( Date, Long/Short ) [Version 1.6 and above]

Returns the name of the month in long/short format.

Example

Dim( a )
SetVar( @a, GetMonthName(!table1.date1, Short ) )

LAST_DAY (d)

Returns the last day of the specified month.

Example

LAST_DAY (TO_DATE('4/15/01')) - returns 30

MONTH (d)

Returns the month of the specified date.

Example

MONTH (TO_DATE('4/15/01')) - returns 4

SYSDATE ()

Returns the system date in your locale date format.

Example

SYSDATE ()

SYSDATETIME ()

Returns the system date and time in your locale date/time format.

Example

SYSDATETIME ()

SYSTIME ()

Returns the system time in your locale time format.

Example

SYSTIME ()