Conversion Functions
The conversion functions convert an expression to a particular data type. The CONVERT function is best used when converting between a value and its text representation. The CAST function gives more control over the resulting data type but gives less control over character formatting. Note that the CONVERT function result supports only a subset of the relational types.
The CAST function converts an expression to a 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).
If both the input and the output are character strings, the output from CAST or CONVERT has the same collation as the input string.
Conversions between CHAR, VARCHAR, or LONGVARCHAR and NCHAR, NVARCHAR, or NLONGVARCHAR assume that CHAR values are encoded using the database code page.
Table 52
Conversion Functions
Function
Description
CAST (exp AS type)
Converts exp to the type indicated
Type may be any of the PSQL relational data types as listed in the table PSQL Transactional and Relational Data Types. The table also lists precison and scale parameters.
CONVERT (exp, type [, style ])
Converts exp to the type indicated. The possible type arguments are:
SQL_BIGINT
SQL_BINARY
SQL_BIT
SQL_CHAR
SQL_DATE
SQL_DATETIME
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GUID
SQL_INTEGER
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TIME
SQL_TIMESTAMP
SQL_TINYINT
SQL_VARCHAR
SQL_WCHAR
SQL_WLONGVARCHAR
SQL_WVARCHAR
The CONVERT arguments use "SQL_" as a prefix for the data type. The PSQL relational data types do not include the "SQL_" prefix. Precision and scale take default values.
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 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 CHAR, and string data to DATE, TIME, and 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 DATE then adds 31 to 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 canonical format: yyyy-mm-dd hh:mm:ss.