SQL Functions
SQL Functions
SQL functions are implemented in the CData Connect Cloud query API. As a result, these functions are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.
The API interprets all SQL function inputs as either strings or column identifiers. All literals must be escaped as strings with single quotes. For example, contrast the SQL Server syntax and provider syntax for the DATENAME function:
- SQL Server:
SELECT DATENAME(yy,GETDATE())
- CData Connect Cloud:
SELECT DATENAME('yy',GETDATE())
String Functions
These functions perform string manipulations and return a string value. See STRING Functions for more details.
SELECT CONCAT(firstname, space(4), lastname) FROM Account WHERE Industry = 'Floppy Disks'
Date Functions
These functions perform date and date time manipulations. See DATE Functions for more details.
SELECT CURRENT_TIMESTAMP() FROM Account
Date Literal Functions
These functions can be used to filter date fields using relative intervals. See Date Literal Functions for more details.
SELECT * FROM MyTable WHERE MyDateField = YESTERDAY()
Math Functions
These functions provide mathematical operations. See MATH Functions for more details.
SELECT RAND() FROM Account
Aggregate Functions
Aggregate functions return data from across multiple rows. See Aggregate Functions for more details.
SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name
Function Parameters and Nesting SQL Functions
The provider supports column names, constants, and results of other functions as parameters to functions. The following are all valid uses of SQL functions:
SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM Account