CAST function (SQL-92 compatible)
Syntax
CAST ( { expression | NULL } AS data_type [(length)] )
Description
The scalar function CAST converts an expression to another data type. The first argument is the expression to be converted. The second argument is the target data type.
The length option for the data_type argument specifies the length for conversions to character data types. If omitted, the default length is 30 bytes.
If the expression evaluates to null, the result of the function is null. Specifying NULL with the CAST function is useful for set operations such as UNION that require two tables to have the same structure. CAST NULL allows you to specify a column of the correct data type so a table with a similar structure to another, but with fewer columns, can be in a union operation with the other table.
The CAST function provides a data-type-conversion mechanism compatible with the SQL-92 standard.
Use the CONVERT function, enclosed in the ODBC escape clause {fn }, to specify ODBC-compliant syntax for data type conversion. See CONVERT function (ODBC compatible) for more information.
Example
The following c-treeSQL example uses CAST to convert an integer field from a catalog table to a character data type:
SELECT CAST(fld AS CHAR(25)), fld FROM admin.syscalctable;
CONVERT(CHARACTER(25),FLD) FLD
-------------------------- ---
100 100
1 record selected
CONVERT function (extension)
Syntax
CONVERT ( { 'data_type[(length)]' | data_type[(length)] }, expression )
Description
The scalar function CONVERT converts an expression to another data type. The first argument is the target data type. The second argument is the expression to be converted to that type.
As indicated in the syntax, single quotes surrounding the data type are optional.
The length option for the data_type argument specifies the length for conversions to character types. If omitted, the default length is 30.
If the expression evaluates to null, the result of the function is null.
The CONVERT function syntax is similar to but not compatible with the ODBC CONVERT function. Enclose the function in the ODBC escape clause {fn }, to specify ODBC-compliant syntax. See CONVERT function (ODBC compatible) for more information.
Examples
The following FairCom DB SQL examples convert an integer field from a catalog table to a character string:
SELECT CONVERT('CHAR', fld), fld FROM admin.syscalctable;
CONVERT(CHAR,FLD) FLD
----------------- ---
100 100
1 record selected
SELECT CONVERT('CHAR(35)', fld), fld
FROM admin.syscalctable;
CONVERT(CHAR(35),FLD) FLD
--------------------- ---
100 100
1 record selected
SELECT CONVERT(CHAR(50), fld), fld FROM systpe.syscalctable;
CONVERT(CHARACTER,FLD) FLD
---------------------- ---
100 100
1 record selected
CONVERT function (ODBC compatible)
Syntax
{fn CONVERT (expression , data_type ) }
data_type::
SQL_BIGINT
| SQL_BINARY
| SQL_BIT
| SQL_CHAR
| SQL_DATE
| SQL_DECIMAL
| SQL_DOUBLE
| SQL_FLOAT
| SQL_INTEGER
| SQL_LONGVARBINARY
| SQL_LONGVARCHAR
| SQL_REAL
| SQL_SMALLINT
| SQL_TIME
| SQL_TIMESTAMP
| SQL_TINYINT
| SQL_VARBINARY
| SQL_VARCHAR
Description
The ODBC scalar function CONVERT converts an expression to another data type. The first argument is the expression to be converted. The second argument is the target data type.
If the expression evaluates to null, the result of the function is null.
The ODBC CONVERT function provides ODBC-compliant syntax for data type conversion. You must enclose the function with the ODBC escape clause {fn } to use ODBC-compliant syntax.
TO_CHAR function (extension)
Syntax
TO_CHAR ( expression [ , format_string ] )
Description
The scalar function TO_CHAR converts the given expression to character form and returns the result. The primary use for TO_CHAR is to format the output of date-time expressions through the format_string argument.
Arguments
- expression - Specifies the expression to be converted to character form. To use the format_string argument, expression must evaluate to a date or time value.
- format_string - A date-time format string that specifies the format of the output. See Date Format Strings and Time Format Strings for more details.
FairCom DB SQL ignores the format string if the expression argument does not evaluate to a date or time.
Example
SELECT C1 FROM T2;
C1
--
09/29/1952
1 record selected
SELECT TO_CHAR(C1, 'Day, Month ddth'),
TO_CHAR(C2, 'HH12 a.m.') FROM T2;
TO_CHAR(C1,DAY, MONTH DDTH) TO_CHAR(C2,HH12 A.M.)
--------------------------- ---------------------
Monday , September 29th 02 p.m.
1 record selected
Notes
- The first argument to the function can be of any type.
- The second argument, if specified, must be of type NCHAR or NVARCHAR for UNICODE builds and of type CHAR or VARCHAR for ANSI builds.
- The result is of character type.
- The format argument can be used only when the type of the first argument is DATE.
- If any of the argument expressions evaluates to null, the result is null.
TO_DATE function (extension)
Syntax
TO_DATE ( date_lit )
Description
The scalar function TO_DATE converts the given date literal to a date value.
Example
SELECT * FROM orders
WHERE order_date <= TO_DATE ('12/31/1991') ;
Notes
- The result is of type DATE.
- Supply the date literal in any valid format. See Date Literals for valid formats of DATE literals.
TO_NUMBER function (extension)
Syntax
TO_NUMBER ( char_expression )
Description
The scalar function TO_NUMBER converts the given character expression to a number value.
Example
SELECT * FROM customer
WHERE TO_NUMBER (SUBSTR (phone, 1, 3)) = 603 ;
Notes
- The argument to the function must be of type NCHAR or NVARCHAR for Unicode builds and of type CHAR or VARCHAR for ANSI builds.
- The result is of type NUMERIC.
- If any of the argument expressions evaluates to null, the result is null.
TO_TIME function (extension)
Syntax
TO_TIME ( time_lit )
Description
The scalar function TO_TIME converts the given time literal to a time value.
Example
SELECT * FROM orders
WHERE order_date < TO_DATE ('05/15/1991')
AND order_time < TO_TIME ('12:00:00') ;
Notes
- The result is of type TIME.
- Supply the time literal in any valid format. See Time Literals for valid formats of TIME literals.
TO_TIMESTAMP function (extension)
Syntax
TO_TIMESTAMP ( timestamp_lit )
Description
The scalar function TO_TIMESTAMP converts the given timestamp literal to a timestamp value.
Example
SELECT * FROM orders
WHERE order_timestamp > TO_TIMESTAMP('4/18/95 10:41:19')
Notes
- The result is of type TIMESTAMP.
- Supply the timestamp literal in any valid format. See Timestamp Literals for valid formats of TIMESTAMP literals.
TRUNCATE function
Syntax
TRUNCATE( number, length )
Description
The scalar function truncates number to length decimal places.
If value of length is positive, number is truncated to the length of places to the right of the decimal point.
If length is negative, number is truncates to positive value of length places to the left of decimal point
Arguments
- number - This is an expression of an exact numeric or approximate numeric data type.
- length - Is the precision to which number is truncated.
Example
SELECT price, TRUNCATE(price, 1), TRUNCATE(price, -1)
FROM custorder;
PRICE TRUNCATE( TRUNCATE(
----- --------- ---------
1546.56 1546.50 1540.00