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 5-6 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 datat type. A style value may be either "20" or "120." 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.