SQL Conversion Functions
CASTASCONNXTYPE(CONNXTypeName, ColumnName, [[Precision] [,Scale]])
Converts the specified column using the supplied CONNX Data type, with an optional precision and scale for those CONNX data type that required it.
Example:
select
CastAsCONNXType('PACKED Decimal -> Decimal', numcol, 4, 1) ,
CastAsCONNXType('Text Date (YYYYMMDD)', stringdate)
from trixie.dbo.test2
CNXRawConvert(CONNXTypeName, ColumnName [[,Offset] [,Length] [,Precision] [,Scale] [,Codepage]])
Converts the specified char or binary expression using the supplied CONNX Data type, with an optional offset, length, codpage, precision and scale for those CONNX data type that required it.
Example:
select
CNXRawConvert('PACKED Decimal -> Decimal', binarycol, 4, 3) ,
CNXRawConvert('Text Date (YYYYMMDD)', stringvalue)
from trixie.dbo.test2
CONVERT(exp,datatype)
Converts the expression exp to the specified data type. Conversion can potentially cause truncation as shown in the examples below.
If the size parameter is omitted for the following data types, the parameter is defaulted to a size of one: Char, Varchar, Binary, Varbinary, Nchar, and Nvarchar.
Valid data types are shown in the following table:
Data Type | Description |
Bigint | 8-byte integer |
Integer | 4-byte integer |
Smallint | 2-byte integer |
Tinyint | 1-byte integer |
Char(size) | Fixed length character string |
Varchar(size) | Variable length character string |
Binary(size) | Fixed length binary string |
Varbinary(size) | Variable length binary string |
Date | ODBC date |
Time | ODBC time |
Timestamp | ODBC date and time |
Double | 8-byte double |
Float | 4-byte real |
Bit | bit |
Decimal(precision, scale) | Decimal |
Numeric(precision, scale) | Numeric |
Nchar(size) | Unicode |
Nvarchar(size) | Variable length Unicode |
Examples:
Function | Returned value |
CONVERT("123", integer) | 123 |
CONVERT(123, char(3)) | 123 |
CONVERT(123, char(2)) | 12 |
CONVERT(123, char) | 1 |