Scalar functions

D3 ODBC supports three types of scalar functions: String, Numeric, and Date/Time.

The syntax required to use a scalar function in a select list or where clause is:

{fn scalar-function (parameters)}

For example,

select {fn DAYNAME(birthdate)} from clients

String functions

These string functions are supported:

String Functions Description
ASCII(string) Returns ASCII code of first character.
CONCAT(string, string) Concatenates the strings.
LCASE(string) Converts string to lowercase.
LEFT(string, count) Returns a substring containing count chars from the beginning of the string.
LENGTH(string) Returns number of chars in string.
RIGHT(string, count) Returns a substring containing count chars from the end of the string.
RTRIM(string) Returns the string without trailing blanks.
SOUNDEX(string) Returns the 4 digit SOUNDEX code for string.
SPACE(count) Returns a string composed of count spaces.
SUBSTRING(string, start, length) Returns a substring beginning at start that is length characters.
UCASE(string) Converts string to uppercase.

Numeric functions

These numeric functions are supported:

Numeric Functions Description
ABS(numeric) Returns the absolute value.
ACOS(float) Returns the arccosine.
ASIN(float) Returns the arcsine.
ATAN(float) Returns the arctangent.
CEILING(numeric) Returns the smallest integer greater than or equal to numeric.
COS(float) Returns the cosine.
DEGREES(numeric) Converts radians to degrees.
EXP(float) Returns the exponential value.
FLOOR(numeric) Returns the largest integer less than or equal to numeric.
LOG(float) Returns the natural logarithm.
MOD(integer, integer) Returns the remainder of the first integer divided by the second integer.
PI() Returns the constant value of PI.
POWER(numeric, integer) Returns the value of numeric to the power integer.
RADIANS(numeric) Converts degrees to radians.
RAND() RAND(seed) Returns a random floating point value.
SIGN(numeric) Returns –1 for numbers less than 0, +1 for numbers greater than 0, and 0 for zero.
SIN(float) Returns the sine.
SQRT(float) Returns the square root.
TAN(float) Returns the tangent.

Time/Date functions

These time/date functions are supported:

Time/Date Functions Description
CURDATE() Returns current date.
CURTIME() Returns current time.
DAYNAME(date) Returns Sunday through Saturday.
DAYOFMONTH(date) Returns 1 through 31.
DAYOFWEEK(date) Returns 1 (Sunday) through 7 (Saturday).
DAYOFYEAR(date) Returns 1 through 366.
HOUR(time) Returns 0 through 23.
MINUTE(time) Returns 0 through 59.
MONTH(date) Returns 1 through 12.
MONTHNAME(date) Returns January through December.
NOW() Returns current timestamp.
QUARTER(date) Returns 1 through 4.
SECOND(time) Returns 0 through 59.
WEEK(date) Returns 1 through 53.
YEAR(date) Returns year as yyyy.