Convert Macro Function (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Preprocessing Command Text (FireDAC)



The following table presents the CONVERT macro function:

Function

Description

Local expression engine

CONVERT(value_exp, data_type) The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords. +


The supported keywords:

BIGINT REAL
BINARY SMALLINT
BIT DATE
CHAR TIME
DECIMAL TIMESTAMP
TIMESTAMP TIMESTAMP
FLOAT VARBINARY
GUID VARCHAR
INTEGER WCHAR
LONGVARBINARY WLONGVARCHAR
LONGVARCHAR WVARCHAR
NUMERIC


The syntax for the explicit data type conversion function does not support specification of conversion format. The argument value_exp can be a column name, the result of another scalar function, or a numeric or string literal. For example:

{ fn CONVERT( { fn CURDATE() }, CHAR ) }

converts the output of the CURDATE scalar function to a character string. Because FireDAC does not mandate a data type for return values from scalar functions (because the functions are often data source–specific), applications should use the CONVERT scalar function whenever possible to force data type conversion. The following two examples illustrate the use of the CONVERT function. These examples assume the existence of a table called EMPLOYEES, with an EMPNO column of type SQL_SMALLINT and an EMPNAME column of type SQL_CHAR. If an application specifies the following SQL statement:

SELECT EMPNO FROM EMPLOYEES WHERE {fn CONVERT(EMPNO,CHAR)} LIKE '1%'
  • A driver for ORACLE translates the SQL statement to:
SELECT EMPNO FROM EMPLOYEES WHERE to_char(EMPNO) LIKE '1%'
  • A driver for SQL Server translates the SQL statement to:
SELECT EMPNO FROM EMPLOYEES WHERE convert(char,EMPNO) LIKE '1%'


If an application specifies the following SQL statement:

SELECT {fn ABS(EMPNO)}, {fn CONVERT(EMPNAME,SMALLINT)} FROM EMPLOYEES WHERE EMPNO <> 0

A driver for ORACLE translates the SQL statement to:

SELECT abs(EMPNO), to_number(EMPNAME) FROM EMPLOYEES WHERE EMPNO <> 0

A driver for SQL Server translates the SQL statement to:

SELECT abs(EMPNO), convert(smallint, EMPNAME) FROM EMPLOYEES WHERE EMPNO <> 0