When querying a View in Intelligence Central or when writing a cross-connection query, Toad allows you to use Intelligence Central functions instead of the usual database-specific functions. Toad then translates the function into the database specific syntax, if necessary.
This method can be convenient if you want to use the same query against two different database types. This method can also be useful when writing cross-connection queries.
Note: This feature is provided as an optional method. For best results when creating a cross-connection query, use the Query Builder to generate the correct syntax.
You can use the Intelligence Central functions in the following situations:
You can use Intelligence Central functions in the situations described above against the following database types:
The following functions can be used in place of platform-specific functions in queries against the supported data sources listed in this topic.
| Category | Function | Description |
|---|---|---|
| Date and Time | CURDATE(), CURRENT_DATE, CURRENT_DATE() | Returns the current date |
| CURTIME(), CURRENT_TIME, CURRENT_TIME() | Returns the current time | |
| NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() | Returns the current date and time | |
| LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP | Returns the current date and time | |
| DATE(expr) | Extracts and returns the date from the expression | |
| TIME(expr) | Extracts and returns the time from the expression | |
| TIMESTAMP(expr) |
Returns the date (or datetime) expression as a datetime value With two arguments, a time expression expr2 is added to a date or datetime expression expr1 | |
| MAKEDATE(year,day-of-year) | Returns the date created from the year and day-of-year arguments | |
| MAKETIME(hour,minute,second) | Returns the time created from the hour, minute, and second arguments | |
|
ADDDATE(date,INTERVAL exp unit), DATE_ADD(date,INTERVAL exp unit) |
Adds a time value (interval) to a date value Example: ADDDATE('2014-06-01',INTERVAL 1 DAY) returns '2014-06-02' | |
| DATE_FORMAT(date,format) | Formats the date according to the format specified | |
| YEAR(date) | Returns the year for date | |
| QUARTER(date) | Returns the quarter of the year for date, in the range 1 to 4 | |
| MONTH(date) |
Returns the month for date | |
| HOUR(time) | Extracts and returns the hour from time | |
| MINUTE(time) | Extracts and returns the minutes from time | |
| SECOND(time) | Extracts and returns the seconds from time | |
| DAYOFMONTH(date), DAY(date) | Returns the day of the month for date | |
| DAYOFYEAR(date) | Returns the day of the year for date, in the range 1 to 366 | |
| TO_DAYS(date) | Returns the number of days between date and year 0 | |
| TO_SECONDS(expr) | (Supported for Oracle only) If expr is a date or datetime, returns the number of seconds between expr and the year 0 | |
| MICROSECOND(expr) | Returns the microseconds from a date or datetime expression, in the range 0 to 999999 | |
| MONTHNAME(date) | Returns the name of the month for date | |
| DAYNAME(date) | Returns the name of the weekday for date | |
|
SUBDATE((date,INTERVAL exp unit)), DATE_SUB((date,INTERVAL exp unit)) |
Subtracts a time value (interval) from a date value | |
| ADDTIME(expr1,expr2) | (Supported for SQL Server only) If expr1 is a time or datetime expression and expr2 is a time expression, adds the expressions and returns the result | |
| SUBTIME(expr1,expr2) | (Supported for SQL Server only) If expr1 is a time or datetime expression and expr2 is a time expression, returns the result of expr1 - expr2 | |
| TIMESTAMPADD(unit,interval,datetime_expr) | Adds an interval to a date or datetime expression | |
| TIMESTAMPDIFF(unit,expr1,expr2) |
(Supported for SQL Server only) Returns the result of expr2 - expr1 where the expressions are date or datetime expressions. The unit for the result (an integer) is determined by unit. Example: TIMESTAMPDIFF(MONTH,'2014-01-01','2014-06-01') returns 5 | |
| String | CONCAT(str1,str2,...) | Returns the string result from concatenating the arguments |
| LENGTH(str) | Returns the length of the string, in bytes | |
| LOCATE(substr,str) | Returns the position of the first occurrence of the sub-string substr in the string str
Example: LOCATE('cde', 'abcdef') returns 3 | |
| LOCATE(substr,str,pos) |
Returns the position of the first occurrence of the sub-string after the position specified by pos Example: LOCATE('cde','abcdefabcdef',6) returns 9 | |
|
SUBSTR(str,pos) SUBSTRING(str,pos) |
Returns a sub-string from the string str starting at the position specified by pos | |
|
SUBSTR(str,pos,len) SUBSTRING(str,pos,len) |
Returns a sub-string from the string str starting at the position specified by pos and containing the number of characters specified by len | |
| LEFT(str,len) |
Returns a string containing the number of characters specified by len from the left side of the string str | |
| RIGHT(str,len) | Returns a string containing the number of characters specified by len from the right side of the string str | |
| REPLACE(str,from_str,to_str) | Returns the string str with each instance of the string from_str replaced by the string to_str | |
| UPPER(str) | Returns the string str with all characters in uppercase | |
| LOWER(str) | Returns the string str with all characters in lowercase | |
| TRIM(str) |
Returns the string with leading and trailing spaces removed | |
| LTRIM(str) | Returns the string with leading spaces removed | |
| RTRIM(str) | Returns the string with trailing spaces removed | |
| Flow Control | IFNULL(expr1,expr2) |
If expr1 is NULL, this function returns expr2. If expr1 is not NULL, this function returns expr1. |
| Select statement clauses | GROUP BY ordinal | Group by the column in the position specified by ordinal |
| Limit count | Limits the number of rows returned to the number specified by count |
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
