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
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. |
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. |
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. |