Conversion Functions
The conversion functions convert an expression to a data type. The CONVERT function can convert data only to an ODBC data type. Note that more than one Pervasive PSQL data type may be mapped to the same ODBC type. For example, DECIMAL, CURRENCY, and BIGINT are all mapped to the ODBC data type SQL_DECIMAL.
The CAST function converts an expression to a Pervasive PSQL relational data type (provided that the expression can be converted to the data type). The CAST function can convert binary zeros in a string. For example, CAST(c1 AS BINARY(10)), where c1 is a character column that contains binary zeros (NULLs).
You cannot CAST or CONVERT user-defined data types.
If both the input and the output are character strings, the output from CAST or CONVERT has the same collation as the input string.
Table 60
Conversion Functions
Function
Description
CAST (exp AS type)
Converts exp to the type indicated
Type may be any of the Pervasive PSQL relational data types as listed in the table Pervasive PSQL Data Types and Equivalent ODBC Data Types.
CONVERT (exp, type [, style ])
Converts exp to the type indicated. The possible types are:
SQL_BIGINT
SQL_BINARY
SQL_BIT
SQL_CHAR
SQL_DATE
SQL_DATETIME
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GUID
SQL_INTEGER
SQL_LONGVARCHAR
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TIME
SQL_TIMESTAMP
SQL_TINYINT
SQL_VARCHAR
SQL_LONGVARBINARY
The optional parameter style applies only to the DATETIME data type. Use of the parameter truncates the milliseconds portion of the DATETIME data type. A style value may be either "20" or "120." Both values specify the ODBC canonical format: yyyy-mm-dd hh:mm:ss. See Examples below.
Examples
The following example casts a DATE to a CHAR.
CREATE TABLE u1(cdata DATE)
INSERT INTO u1 VALUES(curdate())
SELECT CAST(cdate as CHAR20) FROM u1
If the current date were January 1, 2004, the SELECT returns 2004-01-01.
============ 
The following example converts, respectively, a UBIGINT to a SQL_CHAR, and string data to SQL_DATE, SQL_TIME, and SQL_TIMESTAMP.
SELECT CONVERT(id , SQL_CHAR), CONVERT( '1995-06-05', SQL_DATE), CONVERT('10:10:10', SQL_TIME), CONVERT('1990-10-10 10:10:10', SQL_TIMESTAMP) FROM Faculty
============ 
The following example converts a string to SQL_DATE then adds 31 to SQL_DATE.
SELECT Name FROM Class WHERE Start_date > CONVERT ('1995-05-07', SQL_DATE) + 31
============ 
The following examples show how to cast and convert a UNIQUEIDENTIFIER data type.
CREATE TABLE table1(col1 CHAR(36), col2 UNIQUEIDENTIFIER DEFAULT NEWID())
 
INSERT INTO table1 (col1) VALUES ('1129619D-772C-AAAB-B221-00FF00FF0099')
 
SELECT CAST(col1 AS UNIQUEIDENTIFIER) FROM table1
 
SELECT CAST(col2 AS LONGVARCHAR) FROM table1
 
SELECT CONVERT(col2 , SQL_CHAR) FROM table1
 
SELECT CONVERT('1129619D-772C-AAAB-B221-00FF00FF0099' , SQL_GUID) FROM table1
============ 
The following examples show how to convert a DATETIME data type with and without the style parameter.
CREATE TABLE table2(col1 DATETIME)
 
INSERT INTO table2 (col1) VALUES ('2006-12-25 10:10:10.987')
 
SELECT CONVERT(col1 , SQL_CHAR, 20) FROM table2
This returns 2006-12-25 10:10:10.
SELECT CONVERT(col1 , SQL_CHAR, 120) FROM table2
This returns 2006-12-25 10:10:10.
SELECT CONVERT(col1 , SQL_CHAR) FROM table2
This returns 2006-12-25 10:10:10.987.
If you want to include the DATETIME milliseconds, omit the style parameter.
Note the following requirements when using the style parameter:
•
The type parameter must be SQL_CHAR. Any other data type is ignored.
•
The column data type of the expression must be DATETIME.
•
The only permissible style values are 20 and 120. Any other value returns an error. The values 20 or 120 specify the ODBC canonical format: yyyy-mm-dd hh:mm:ss.